ANSI_MODE
Platí pro:
Databricks SQL
Parametr ANSI_MODE konfigurace řídí klíčové chování vestavěných funkcí a operací přetypování.
Tento článek popisuje režim ANSI v Databricks SQL. Informace o dodržování předpisů ANSI v Databricks Runtime najdete v tématu Dodržování předpisů ANSI v Databricks Runtime.
PRAVDIVÝ
Dodržuje standard SQL v tom, jak se zabývá určitými aritmetickými operacemi a převody typů, podobně jako většina databází a datových skladů. Dodržování tohoto standardu podporuje lepší kvalitu dat, integritu a přenositelnost.
FALEŠNÝ
Databricks SQL používá chování kompatibilní s Hivem.
Tento parametr můžete nastavit na úrovni relace pomocí příkazu SET a na globální úrovni pomocí konfiguračních parametrů SQL nebo pomocí rozhraní SQL Warehouse API .
Výchozí hodnota systému je TRUE pro účty přidané v Databricks SQL 2022.35 a vyšší.
Referenční dokumentace k Sql Databricks popisuje standardní chování SQL.
Následující části popisují rozdíly mezi ANSI_MODETRUE (režimEM ANSI) a FALSE (bez režimu ANSI).
V módu jiném než ANSI mohou aritmetické operace prováděné s číselnými typy vracet přetečené hodnoty nebo hodnotu NULL, zatímco v módu ANSI tyto operace vrací chybu.
| Operátor | Popis | Příklad | ANSI_MODE = pravda | ANSI_MODE = nepravda |
|---|---|---|---|---|
| dělenec / dělitel | Vrátí podíl dělence děleného dělitelem. | 1/0 |
Chyba | NULL |
| - výraz | Vrátí negovanou hodnotu výrazu. | -(-128y) |
Chyba |
-128y (Přetečení) |
| výraz1 – výraz2 | Vrátí výsledek odečtení výrazu 2 od výrazu 1. | -128y - 1y |
Chyba |
127y (Přetečení) |
| výraz1 + výraz2 | Vrátí součet výrazu1 a výrazu2. | 127y + 1y |
Chyba |
-128y (Přetečení) |
| dividend děleno dělitelem v procentech | Vrátí zbytek po děliteli nebo děliteli. | 1 % 0 |
Chyba | NULL |
| multiplikátor * multiplikand | Vrátí násobitel vynásobený násobením. | 100y * 100y |
Chyba |
16y (Přetečení) |
| arrayExpr[index] | Vrátí prvek arrayExpr v indexu. | Neplatný index pole | Chyba | NULL |
| mapExpr[key] | Vrátí hodnotu mapExpr pro daný klíč. | Neplatný klíč mapy | Chyba | NULL |
| dělitel div dividenda | Vrátí celočíselnou část dělení dělence dělitelem. | 1 div 0 |
Chyba | NULL |
Chování některých předdefinovaných funkcí se může lišit v režimu ANSI v režimu jiné než ANSI za podmínek uvedených níže.
| Operátor | Popis | Podmínka | ANSI_MODE = pravda | ANSI_MODE = nepravda |
|---|---|---|---|---|
| abs(výraz) | Vrátí absolutní hodnotu číselné hodnoty ve výrazu. | abs(-128y) |
Chyba |
-128y (Přetečení) |
| element_at(mapExpr, klíč) | Vrátí hodnotu výrazu mapExpr pro zadaný klíč. | Neplatný klíč mapy | Chyba | NULL |
| element_at(arrayExpr, index) | Vrátí prvek arrayExpr v indexu. | Neplatný index pole | Chyba | NULL |
| elt(index, výraz1 [, ...] ) | Vrátí n-tý výraz. | Neplatný index | Chyba | NULL |
| make_date(y;m;d) | Vytvoří datum z polí rok, měsíc a den. | Neplatné datum výsledku | Chyba | NULL |
| make_timestamp(y,m,d,h,mi,s[;tz]) | Vytvoří časové razítko z polí. | Neplatné časové razítko výsledku | Chyba | NULL |
| make_interval(y,m,w,d,h,mi;s) | Vytvoří interval z polí. | Neplatný interval výsledku | Chyba | NULL |
| mod(dividenda, dělitel) | Vrátí zbytek po dělení dividenda / dělitelem. | mod(1, 0) |
Chyba | NULL |
| next_day(výraz,denVTýdnu) | Vrátí první datum, které je pozdější než expr a nese název jako v dayOfWeek. | Neplatný den v týdnu | Chyba | NULL |
| parse_url(url, partToExtract[, klíč]) | Extrahuje část z adresy URL. | Neplatná adresa URL | Chyba | NULL |
| pmod(dividenda, dělitel) | Vrátí kladný zbytek po dělení čísel dividend/divisor. | pmod(1, 0) |
Chyba | NULL |
| velikost(výraz) | Vrátí kardinalitu výrazu. | size(NULL) |
NULL |
-1 |
| to_date(výraz[;fmt]) | Vrátí výraz převedený na datum pomocí volitelného formátování. | Neplatný výraz nebo formátovací řetězec | Chyba | NULL |
| to_timestamp(výraz[;fmt]) | Vrátí výraz převedený na časové razítko pomocí volitelného formátování. | Neplatný výraz nebo formátovací řetězec | Chyba | NULL |
| to_unix_timestamp(výraz[;fmt]) | Vrátí časové razítko ve výrazu „expr“ jako UNIX časové razítko. | Neplatný výraz nebo formátovací řetězec | Chyba | NULL |
| unix_timestamp([výraz[; fmt]]) | Vrátí časové razítko systému UNIX aktuálního nebo zadaného času. | Neplatný výraz nebo formátovací řetězec | Chyba | NULL |
Pravidla a chování týkající se CAST jsou v režimu ANSI přísnější. Mohou být rozděleny do následujících tří kategorií:
| Typ zdroje | Typ cíle | Příklad | ANSI_MODE = pravda | ANSI_MODE = nepravda |
|---|---|---|---|---|
| Booleanovská hodnota | Časové razítko | cast(TRUE AS TIMESTAMP) |
Chyba | 1970-01-01 00:00:00.000001 UTC |
| Datum | Logická hodnota | cast(DATE'2001-08-09' AS BOOLEAN) |
Chyba | NULL |
| Časové razítko | Logická hodnota | cast(TIMESTAMP'1970-01-01 00:00:00Z' AS BOOLEAN) |
Chyba | FALSE |
| Integrální číslo | Binární | cast(15 AS BINARY) |
Chyba | binární reprezentace |
| Typ zdroje | Typ cíle | Podmínka | Příklad | ANSI_MODE = pravda | ANSI_MODE = nepravda |
|---|---|---|---|---|---|
| String | Neřetězcový | Neplatný vstup | cast('a' AS INTEGER) |
Chyba | NULL |
| Pole, Struktura, Mapa | Pole, Struktura, Mapa | Neplatný vstup | cast(ARRAY('1','2','3') AS ARRAY<DATE>) |
Chyba | NULL |
| Numerický | Číslo | Přetečení | cast(12345 AS BYTE) |
Chyba | NULL |
| Numerický | Integrální číslo | Zkrácení | cast(5.1 AS INTEGER) |
Chyba | 5 |
Poznámka
U každého z těchto přetypování můžete místo cast použít try_cast, abyste vrátili NULL místo chyby.
V části ANSI_MODE = TRUEDatabricks SQL používá jasná pravidla přetypování datových typů SQL pro:
Naproti tomu ANSI_MODE = FALSE je nekonzistentní a více lenientní. Příklad:
- Při použití
STRINGtypu s libovolným aritmetickým operátorem je řetězec implicitně přetypován naDOUBLE. - Při porovnávání
STRINGs libovolným číselným typem se řetězec implicitně přetypuje na ten typ, s kterým se porovnává. - Při provádění
UNION,COALESCEnebo jiných operací, kde musí být nalezen nejmenší společný typ, jsou všechny typy přetypovány naSTRING, pokud existuje nějakýSTRINGtyp.
Databricks doporučuje použít explicitní funkce cast nebo try_cast namísto spoléhání se na 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