ANSI_MODE
Область применения: 
Databricks SQL
Параметр конфигурации ANSI_MODE управляет ключевым поведением встроенных функций и операций приведения.
В этой статье описывается режим ANSI в Databricks SQL. Сведения о соответствии ANSI в Databricks Runtime см. в разделе "Соответствие ANSI" в Databricks Runtime.
Настройки
TRUE
Соответствует стандарту SQL в том, как он обрабатывает определенные арифметические операции и преобразования типов, подобно большинству баз данных и хранилищ данных. Следование этому стандарту способствует повышению качества, а также обеспечению целостности и переносимости данных.
FALSE
Databricks SQL поддерживает поведение, совместимое с Hive.
Этот параметр можно задать на уровне сеанса с помощью инструкции SET и на глобальном уровне с помощью параметров конфигурации SQL или API хранилища SQL.
По умолчанию
Системное значение по умолчанию предназначено TRUE для учетных записей, добавленных в Databricks SQL 2022.35 и более поздних версий.
Подробное описание
Справочная документация по Databricks SQL описывает стандартное поведение SQL.
В следующих разделах описаны различия между TRUE ANSI_MODE (режим ANSI) и FALSE (режим, отличный от ANSI).
Операторы
В режиме, отличном от ANSI, арифметические операции, выполняемые над числовыми типами, могут возвращать переполненные значения или NULL, тогда как в режиме ANSI такие операции возвращают ошибку.
| Operator | Description | Пример | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|
| dividend / divisor | Возвращает делимое, деленное на делитель. | 1/0 |
Ошибка | NULL |
| - expr | Возвращает значение, противоположное expr. | -(-128y) |
Ошибка | -128y (переполнение) |
| expr1 - expr2 | Возвращает результат вычитания expr2 из expr1. | -128y - 1y |
Ошибка | 127y (переполнение) |
| expr1 + expr2 | Возвращает сумму expr1 и expr2. | 127y + 1y |
Ошибка | -128y (переполнение) |
| dividend % divisor | Возвращает остаток, полученный в результате деления делимого на делитель. | 1 % 0 |
Ошибка | NULL |
| multiplier * multiplicand | Возвращает множитель, умноженный на множимое. | 100y * 100y |
Ошибка | 16y (переполнение) |
| arrayExpr[index] | Возвращает элемент arrayExpr по индексу. | Недопустимый индекс массива | Ошибка | NULL |
| mapExpr[key] | Возвращает значение mapExpr для ключа. | Недопустимый ключ карты | Ошибка | NULL |
| divisor div dividend | Возвращает целую часть деления делимого на делитель. | 1 div 0 |
Ошибка | NULL |
Функции
Поведение некоторых встроенных функций может отличаться в режиме ANSI и в режиме, отличном от ANSI, при условиях, указанных ниже.
| Operator | Description | Условие | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|
| abs(expr) | Возвращает абсолютное значение числового значения в expr. | abs(-128y) |
Ошибка | -128y (переполнение) |
| element_at(mapExpr, key) | Возвращает значение mapExpr для ключа. | Недопустимый ключ карты | Ошибка | NULL |
| element_at(arrayExpr, index) | Возвращает элемент arrayExpr по индексу. | Недопустимый индекс массива | Ошибка | NULL |
| elt(index, expr1 [, …] ) | Возвращает выражение с порядковым номером n. | Недопустимый индекс | Ошибка | NULL |
| make_date(y,m,d) | Создает дату из полей года, месяца и дня. | Недопустимая итоговая дата | Ошибка | NULL |
| make_timestamp(y,m,d,h,mi,s[,tz]) | Создает метку времени на основе полей. | Недопустимая результирующая метка времени | Ошибка | NULL |
| make_interval(y,m,w,d,h,mi,s) | Создает интервал на основе полей. | Недопустимый результирующий интервал | Ошибка | NULL |
| mod(dividend, divisor) | Возвращает остаток, полученный в результате деления делимого на делитель. | mod(1, 0) |
Ошибка | NULL |
| next_day(expr,dayOfWeek) | Возвращает первую дату, которая позже expr и которая называется dayOfWeek. | Недопустимый день недели | Ошибка | NULL |
| parse_url(url, partToExtract[, key]) | Извлекает часть из url. | Недопустимый URL-адрес. | Ошибка | NULL |
| pmod(dividend, divisor) | Возвращает положительный остаток, полученный в результате деления делимого на делитель. | pmod(1, 0) |
Ошибка | NULL |
| size(expr) | Возвращает кратность expr. | size(NULL) |
NULL |
-1 |
| to_date(expr[,fmt]) | Возвращает результат приведения expr к дате с использованием необязательного форматирования. | Недопустимые строка форматирования или expr | Ошибка | NULL |
| to_timestamp(expr[,fmt]) | Возвращает результат приведения expr к метке времени с использованием необязательного форматирования. | Недопустимые строка форматирования или expr | Ошибка | NULL |
| to_unix_timestamp(expr[,fmt]) | Возвращает метку времени в expr в виде метки времени UNIX. | Недопустимые строка форматирования или expr | Ошибка | NULL |
| unix_timestamp([expr[, fmt]]) | Возвращает метку времени UNIX для текущего или указанного времени. | Недопустимые строка форматирования или expr | Ошибка | NULL |
Приведение правил
Правила и поведение, касающиеся CAST, являются более строгими в режиме ANSI. Их можно разделить на следующие три категории:
Правила преобразования во время компиляции
| Тип источника | Целевой тип | Пример | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|
| Логический | Метка времени | cast(TRUE AS TIMESTAMP) |
Ошибка | 1970-01-01 00:00:00.000001 UTC |
| Дата | Логический | cast(DATE'2001-08-09' AS BOOLEAN) |
Ошибка | NULL |
| Метка времени | Логический | cast(TIMESTAMP'1970-01-01 00:00:00Z' AS BOOLEAN) |
Ошибка | FALSE |
| Целочисленные типы | Binary | cast(15 AS BINARY) |
Ошибка | Двоичное представление |
Ошибки среды выполнения
| Тип источника | Целевой тип | Condition | Пример | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|---|
| Строка | Не строка | недопустимые входные данные | cast('a' AS INTEGER) |
Ошибка | NULL |
| Массив, структура, карта | Массив, структура, карта | недопустимые входные данные | cast(ARRAY('1','2','3') AS ARRAY<DATE>) |
Ошибка | NULL |
| Числовое | Числовое | Overflow | cast(12345 AS BYTE) |
Ошибка | NULL |
| Числовое | Целочисленные типы | Усечение | cast(5.1 AS INTEGER) |
Ошибка | 5 |
Примечание.
Для каждого из этих приведений вы можете использовать try_castвместо cast для возврата NULL, а не ошибки.
Неявные правила приведения типов
В режиме ANSI_MODE = TRUE Databricks SQL использует четкие правила приведения типов данных SQL для:
В отличие от этого ANSI_MODE = FALSE характеризуется несогласованностью и меньшей строгостью. Например:
- При использовании типа
STRINGс любым арифметическим оператором строка неявно приводится кDOUBLE. - При сравнении
STRINGс любым числовым типом строка неявно приводится к сравниваемому типу. - При выполнении
UNION,COALESCEили других операций, где должен быть найден наименее распространенный тип, все типы приводятся кSTRINGпри наличии любого типаSTRING.
В Databricks рекомендуется использовать явную функцию cast или try_cast вместо использования ANSI_MODE = FALSE.
Примеры
> SET ansi_mode = true;
-- Protects against integral numeric overflow
> SELECT cast(12345 AS TINYINT);
Casting 12345 to tinyint causes overflow
-- For invalid values raises errors instead of returning NULL.
> SELECT cast('a' AS INTEGER);
Invalid input syntax for type numeric: a.
To return NULL instead, use 'try_cast'
-- try_cast() is consistent for both modes
> SELECT try_cast('a' AS INTEGER);
NULL
-- Does not allow ambiguous crosscasting.
> SELECT c1 + c2 FROM VALUES('5', '7.6') AS T(c1, c2);
Cannot resolve '(T.c1 + T.c2)' due to data type mismatch:
'(T.c1 + T.c2)' requires (numeric or interval day to second or interval year to month or interval) type, not string
-- Promotes STRING to least common type (STRING, INTEGER --> BIGINT) for arithmetic operation.
> SELECT typeof(5 - '3');
bigint
-- Promotes STRING to least common type (INTEGER, STRING --> BIGINT) with runtime check
> SELECT c1 = c2 FROM VALUES(10, '10.1') AS T(c1, c2);
Invalid input syntax for type numeric: 10.1. To return NULL instead, use 'try_cast'.
-- Promotes STRING to least common type (STRING, INTEGER --> BIGINT) for set operation with runtime check.
> SELECT typeof(c1) FROM (SELECT 5 UNION ALL SELECT '6') AS T(c1);
bigint
bigint
> SET ansi_mode = false;
-- Silent integral numeric overflow
> SELECT cast(12345 AS TINYINT);
57
-- Returns NULL instead of an error
> SELECT cast('a' AS INTEGER);
NULL
-- try_cast() is safe for both modes
> SELECT try_cast('a' AS INTEGER);
NULL
-- Does allow ambiguous crosscasting using DOUBLE.
> SELECT c1 + c2 FROM VALUES('5', '7.6') AS T(c1, c2);
12.6
-- Crosscasts STRING to DOUBLE for arithmetic operation.
> SELECT typeof(5 - '3');
double
-- Implicitly casts STRING to INTEGER equating 10 with 10.1
> SELECT c1 = c2 FROM VALUES(10, '10.1') AS T(c1, c2);
true
-- Promotes to string for set operations
> SELECT typeof(c1) FROM (SELECT 5 UNION ALL SELECT '6') AS T(c1);
string
string