ANSI_MODE
Gäller för:
Databricks SQL
Konfigurationsparametern ANSI_MODE styr viktiga beteenden för inbyggda funktioner och gjutna åtgärder.
I den här artikeln beskrivs ANSI-läge i Databricks SQL. Ansi-efterlevnad i Databricks Runtime finns i ANSI-efterlevnad i Databricks Runtime.
Inställningar
SANT
Följer SQL-standarden i hur den hanterar vissa aritmetiska åtgärder och typkonverteringar, ungefär som de flesta databaser och informationslager. Genom att följa den här standarden får du bättre datakvalitet, integritet och portabilitet.
FALSKA
Databricks SQL använder Hive-kompatibelt beteende.
Du kan ange den här parametern på sessionsnivå med set-instruktionen och på global nivå med hjälp av SQL-konfigurationsparametrar eller SQL Warehouse-API:et.
Systemets standard
Systemets standardvärde är TRUE för konton som läggs till i Databricks SQL 2022.35 och senare.
Detaljerad beskrivning
Referensdokumentationen för Databricks SQL beskriver SQL-standardbeteendet.
I följande avsnitt beskrivs skillnaderna mellan ANSI_MODE TRUE (ANSI-läge) och FALSE (icke-ANSI-läge).
Operatorer
I icke-ANSI-läge kan aritmetiska åtgärder som utförs på numeriska typer returnera spillda värden eller NULL, medan sådana åtgärder i ANSI-läge returnerar ett fel.
| Operatör | Description | Exempel | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|
| utdelning/divisor | Returnerar utdelning dividerat med divisor. | 1/0 |
Fel | NULL |
| -Uttryck | Returnerar det negerade värdet för expr. | -(-128y) |
Fel | -128y (Spill) |
| expr1 - expr2 | Returnerar subtraktionen av expr2 från expr1. | -128y - 1y |
Fel | 127y (Spill) |
| expr1 + expr2 | Returnerar summan av expr1 och expr2. | 127y + 1y |
Fel | -128y (Spill) |
| dividend % divisor | Returnerar resten efter utdelning/divisor. | 1 % 0 |
Fel | NULL |
| multiplikator * multiplicand | Returnerar multiplikator multiplicerad med multiplicand. | 100y * 100y |
Fel | 16y (Spill) |
| arrayExpr[index] | Returnerar elementet i en arrayExpr vid index. | Ogiltigt matrisindex | Fel | NULL |
| mapExpr[key] | Returnerar värdet för mapExpr för nyckeln. | Ogiltig kartnyckel | Fel | NULL |
| divisor div utdelning | Returnerar den integrerade delen av uppdelningen av divisor efter utdelning. | 1 div 0 |
Fel | NULL |
Functions
Beteendet för vissa inbyggda funktioner kan vara olika i ANSI-läge jämfört med icke-ANSI-läge under de villkor som anges nedan.
| Operatör | beskrivning | Villkor | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|
| abs(expr) | Returnerar det absoluta värdet för det numeriska värdet i expr. | abs(-128y) |
Fel | -128y (Spill) |
| element_at(mapExpr, key) | Returnerar värdet för mapExpr för nyckeln. | Ogiltig kartnyckel | Fel | NULL |
| element_at(arrayExpr, index) | Returnerar elementet i en arrayExpr vid index. | Ogiltigt matrisindex | Fel | NULL |
| elt(index, expr1 [, ...] ) | Returnerar det n:e uttrycket. | Ogiltigt index | Fel | NULL |
| make_date(y,m,d) | Skapar ett datum från fälten år, månad och dag. | Ogiltigt resultatdatum | Fel | NULL |
| make_timestamp(y,m,d,h,mi,s[,tz]) | Skapar en tidsstämpel från fält. | Ogiltig resultattidsstämpel | Fel | NULL |
| make_interval(y,m,w,d,h,mi,s) | Skapar ett intervall från fält. | Ogiltigt resultatintervall | Fel | NULL |
| mod(dividend, divisor) | Returnerar resten efter utdelning/divisor. | mod(1, 0) |
Fel | NULL |
| next_day(expr,dayOfWeek) | Returnerar det första datumet som är senare än uttr och som heter i dayOfWeek. | Ogiltig veckodag | Fel | NULL |
| parse_url(url, partToExtract[, key]) | Extraherar en del från URL:en. | Ogiltig URL | Fel | NULL |
| pmod(utdelning, divisor) | Returnerar den positiva resten efter utdelning/divisor. | pmod(1, 0) |
Fel | NULL |
| size(expr) | Returnerar kardinaliteten för uttr. | size(NULL) |
NULL |
-1 |
| to_date(expr[,fmt]) | Returnerar utspr-gjutning till ett datum med en valfri formatering. | Ogiltig expr- eller formatsträng | Fel | NULL |
| to_timestamp(expr[,fmt]) | Returnerar expr cast till en tidsstämpel med en valfri formatering. | Ogiltig expr- eller formatsträng | Fel | NULL |
| to_unix_timestamp(expr[,fmt]) | Returnerar tidsstämpeln i expr som en UNIX-tidsstämpel. | Ogiltig expr- eller formatsträng | Fel | NULL |
| unix_timestamp([expr[, fmt]]) | Returnerar UNIX-tidsstämpeln för aktuell eller angiven tid. | Ogiltig expr- eller formatsträng | Fel | NULL |
Regler för gjutning
Reglerna och beteendena för CAST är striktare i ANSI-läge. De kan delas in i följande tre kategorier:
Kompilera tidskonverteringsregler
| Source type | Måltyp | Exempel | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|
| Booleskt | Tidsstämpel | cast(TRUE AS TIMESTAMP) |
Fel | 1970-01-01 00:00:00.000001 UTC |
| Datum | Booleskt | cast(DATE'2001-08-09' AS BOOLEAN) |
Fel | NULL |
| Tidsstämpel | Booleskt | cast(TIMESTAMP'1970-01-01 00:00:00Z' AS BOOLEAN) |
Fel | FALSE |
| Integral numeriskt | Binära | cast(15 AS BINARY) |
Fel | binär representation |
Körningsfel
| Source type | Måltyp | Villkor | Exempel | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|---|
| String | Icke-sträng | Ogiltiga indata | cast('a' AS INTEGER) |
Fel | NULL |
| Matris, Struct, Karta | Matris, Struct, Karta | Ogiltiga indata | cast(ARRAY('1','2','3') AS ARRAY<DATE>) |
Fel | NULL |
| Numerisk | Numerisk | Spill | cast(12345 AS BYTE) |
Fel | NULL |
| Numerisk | Integral numeriskt | Trunkering | cast(5.1 AS INTEGER) |
Fel | 5 |
Kommentar
För var och en av dessa avgjutningar kan du använda try_cast i stället för cast för att returnera NULL i stället för ett fel.
Regler för implicit typtvång
Under ANSI_MODE = TRUEanvänder Databricks SQL tydliga regler för SQL-datatypsgjutning för:
Däremot ANSI_MODE = FALSE är inkonsekvent och mildare. Till exempel:
- När du använder en
STRINGtyp med en aritmetikoperator omvandlas strängen implicit tillDOUBLE. - När du jämför en
STRINGmed någon numerisk typ omvandlas strängen implicit till den typ som den jämför med. - När du utför en
UNION,COALESCE, eller andra åtgärder där en minst vanlig typ måste hittas är alla typer gjutna tillSTRINGom det finns någonSTRINGtyp.
Databricks rekommenderar att du använder funktionen explicit cast eller try_cast i stället för att förlita sig på ANSI_MODE = FALSE.
Exempel
> 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