ANSI_MODE
Platí pro:
Databricks SQL
Parametr ANSI_MODE konfigurace řídí chování klíčů předdefinovaný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.
Nastavení
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í systém
Výchozí hodnota systému je TRUE pro účty přidané v Databricks SQL 2022.35 a vyšší.
Podrobný popis
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).
Operátory
V režimu jiného typu než ANSI můžou aritmetické operace prováděné s číselnými typy vracet přetečení hodnot nebo HODNOTU NULL, zatímco v režimu ANSI tyto operace vrací chybu.
| Operátor | Description | Příklad | ANSI_MODE = true | ANSI_MODE = nepravda |
|---|---|---|---|---|
| dělitel/ dělitel | Vrátí dělitel dělitele dělitele. | 1/0 |
Chyba | NULL |
| - výraz | Vrátí negated hodnotu výrazu. | -(-128y) |
Chyba |
-128y (Přetečení) |
| výraz1 – výraz2 | Vrátí odčítání výrazu 2 od výrazu1. | -128y - 1y |
Chyba |
127y (Přetečení) |
| výraz1 + výraz2 | Vrátí součet výrazu1 a výraz2. | 127y + 1y |
Chyba |
-128y (Přetečení) |
| dělitel dělitele v procentech dividend | Vrátí zbytek po děliteli nebo děliteli. | 1 % 0 |
Chyba | NULL |
| multiplikátor * multiplikátor | 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 klíč. | Neplatný klíč mapy | Chyba | NULL |
| dělitel div dividenda | Vrátí celočíselnou část dělení dělitele dělitelem dělitelem. | 1 div 0 |
Chyba | NULL |
Funkce
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 | Description | Podmínka | ANSI_MODE = true | 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 mapExpr pro 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í nth 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ěliteli nebo děliteli. | mod(1, 0) |
Chyba | NULL |
| next_day(výraz,dayOfWeek) | Vrátí první datum, které je pozdější než výraz a pojmenovaný jako v denOfWeek. | 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ěliteli nebo děliteli. | pmod(1, 0) |
Chyba | NULL |
| size(výraz) | Vrátí kardinalitu výrazu. | size(NULL) |
NULL |
-1 |
| to_date(výraz[;fmt]) | Vrátí přetypování výrazu 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í přetypování výrazu 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 jako časové razítko systému UNIX. | 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 |
Přetypování pravidel
Pravidla a chování týkající se přetypování jsou v režimu ANSI přísnější. Mohou být rozděleny do následujících tří kategorií:
Pravidla převodu v čase kompilace
| Source type | Typ cíle | Příklad | ANSI_MODE = true | ANSI_MODE = nepravda |
|---|---|---|---|---|
| Logická 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 |
Chyby za běhu
| Source type | Typ cíle | Podmínka | Příklad | ANSI_MODE = true | ANSI_MODE = nepravda |
|---|---|---|---|---|---|
| String | Neřetězcový | Neplatný vstup | cast('a' AS INTEGER) |
Chyba | NULL |
| Array, Struct, Map | Array, Struct, Map | Neplatný vstup | cast(ARRAY('1','2','3') AS ARRAY<DATE>) |
Chyba | NULL |
| Číslo | Číslo | Přetečení | cast(12345 AS BYTE) |
Chyba | NULL |
| Číslo | 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 přetypování použít try_cast, abyste se vrátili NULL místo chyby.
Implicitní pravidla převodu typů
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í libovolného číselného
STRINGtypu se řetězec implicitně přetypuje na typ, se který porovnává. - Při provádění
UNIONoperací ,COALESCEnebo jiných operací, kde musí být nalezen nejméně společný typ, všechny typy jsou přetypována,STRINGpokud existuje nějakýSTRINGtyp.
Databricks doporučuje použít explicitní přetypování
Příklady
> 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