ANSI_MODE
Si applica a:
Databricks SQL
Il ANSI_MODE parametro di configurazione controlla i comportamenti chiave delle funzioni predefinite e delle operazioni di cast.
Questo articolo descrive la modalità ANSI in Databricks SQL. Per la conformità ANSI in Databricks Runtime, vedere Conformità ANSI in Databricks Runtime.
Impostazione
TRUE
Segue lo standard SQL per gestire determinate operazioni aritmetiche e conversioni di tipi, in modo analogo alla maggior parte dei database e dei data warehouse. Seguendo questo standard si promuove una migliore qualità dei dati, integrità e portabilità.
FALSE
Databricks SQL usa un comportamento compatibile con Hive.
È possibile impostare questo parametro a livello di sessione usando l'istruzione edizione Standard T e a livello globale usando i parametri di configurazione SQL o l'API di SQL Warehouse.
Predefinito del sistema
Il valore predefinito del sistema è TRUE per gli account aggiunti in Databricks SQL 2022.35 e versioni successive.
Descrizione dettagliata
La documentazione di riferimento di Databricks SQL descrive il comportamento standard di SQL.
Le sezioni seguenti descrivono le differenze tra ANSI_MODE TRUE (modalità ANSI) e FALSE (modalità non ANSI).
Operatori
In modalità non ANSI, le operazioni aritmetiche eseguite sui tipi numerici possono restituire valori overflow o NULL, mentre in modalità ANSI tali operazioni restituiscono un errore.
| Operatore | Descrizione | Esempio | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|
| dividendo/divisore | Restituisce il dividendo diviso per divisore. | 1/0 |
Error | NULL |
| -Expr | Restituisce il valore negato di expr. | -(-128y) |
Error | -128y (Overflow) |
| expr1 - expr2 | Restituisce la sottrazione di expr2 da expr1. | -128y - 1y |
Error | 127y (Overflow) |
| expr1 + expr2 | Restituisce la somma di expr1 ed expr2. | 127y + 1y |
Error | -128y (Overflow) |
| divisore dividend % | Restituisce il resto dopo dividend/divisor. | 1 % 0 |
Error | NULL |
| moltiplicatore * moltiplicatore | Restituisce il moltiplicatore moltiplicato per multiplicand. | 100y * 100y |
Error | 16y (Overflow) |
| arrayExpr[index] | Restituisce l'elemento di una matriceExpr in corrispondenza dell'indice. | Indice di matrice non valido | Error | NULL |
| mapExpr[key] | Restituisce il valore di mapExpr per la chiave. | Chiave della mappa non valida | Error | NULL |
| dividendo divisore div | Restituisce la parte integrante della divisione del divisore in base al dividendo. | 1 div 0 |
Error | NULL |
Funzioni
Il comportamento di alcune funzioni predefinite può essere diverso in modalità ANSI rispetto alla modalità NON ANSI nelle condizioni specificate di seguito.
| Operatore | Descrizione | Condizione | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|
| abs(expr) | Restituisce il valore assoluto del valore numerico in expr. | abs(-128y) |
Error | -128y (Overflow) |
| element_at(mapExpr, key) | Restituisce il valore di mapExpr per la chiave. | Chiave della mappa non valida | Error | NULL |
| element_at(arrayExpr, index) | Restituisce l'elemento di una matriceExpr in corrispondenza dell'indice. | Indice di matrice non valido | Error | NULL |
| elt(index, expr1 [, ...] ) | Restituisce l'na espressione. | Indice non valido | Error | NULL |
| make_date(y,m,d) | Crea una data dai campi anno, mese e giorno. | Data risultato non valida | Error | NULL |
| make_timestamp(y,m,d,h,mi,s[,tz]) | Crea un timestamp dai campi. | Timestamp del risultato non valido | Error | NULL |
| make_interval(y,m,w,d,h,mi,s) | Crea un intervallo dai campi. | Intervallo di risultati non valido | Error | NULL |
| mod(dividend, divisor) | Restituisce il resto dopo dividend/divisor. | mod(1, 0) |
Error | NULL |
| next_day(expr,dayOfWeek) | Restituisce la prima data successiva a expr e denominata come in dayOfWeek. | Giorno della settimana non valido | Error | NULL |
| parse_url(url, partToExtract[, key]) | Estrae una parte dall'URL. | URL non valido | Error | NULL |
| pmod(dividend, divisor) | Restituisce il resto positivo dopo dividendo/divisore. | pmod(1, 0) |
Error | NULL |
| size(expr) | Restituisce la cardinalità di expr. | size(NULL) |
NULL |
-1 |
| to_date(expr[,fmt]) | Restituisce expr cast a una data utilizzando una formattazione facoltativa. | Stringa di formato o expr non valida | Error | NULL |
| to_timestamp(expr[,fmt]) | Restituisce expr cast a un timestamp usando una formattazione facoltativa. | Stringa di formato o expr non valida | Error | NULL |
| to_unix_timestamp(expr[,fmt]) | Restituisce il timestamp in expr come timestamp UNIX. | Stringa di formato o expr non valida | Error | NULL |
| unix_timestamp([expr[, fmt]]) | Restituisce il timestamp UNIX dell'ora corrente o specificata. | Stringa di formato o expr non valida | Error | NULL |
Regole di cast
Le regole e i comportamenti relativi a CAST sono più rigidi in modalità ANSI. Possono essere suddivisi nelle tre categorie seguenti:
- Regole di conversione in fase di compilazione
- Errori di runtime
- Regole di coercizione dei tipi impliciti
Regole di conversione in fase di compilazione
| Source type | Tipo di destinazione | Esempio | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|
| Booleano | Timestamp: | cast(TRUE AS TIMESTAMP) |
Error | 1970-01-01 00:00:00.000001 UTC |
| Data | Booleano | cast(DATE'2001-08-09' AS BOOLEAN) |
Error | NULL |
| Timestamp: | Booleano | cast(TIMESTAMP'1970-01-01 00:00:00Z' AS BOOLEAN) |
Error | FALSE |
| Numerico integrale | Binario | cast(15 AS BINARY) |
Error | rappresentazione binaria |
Errori di runtime
| Source type | Tipo di destinazione | Condizione | Esempio | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|---|
| String | Non stringa | input non valido | cast('a' AS INTEGER) |
Error | NULL |
| Matrice, Struct, Mappa | Matrice, Struct, Mappa | input non valido | cast(ARRAY('1','2','3') AS ARRAY<DATE>) |
Error | NULL |
| Numeric | Numeric | Overflow | cast(12345 AS BYTE) |
Error | NULL |
| Numerico | Numerico integrale | Troncamento | cast(5.1 AS INTEGER) |
Error | 5 |
Nota
Per ognuno di questi cast è possibile usare try_cast anziché eseguire il cast per restituire NULL anziché un errore.
Regole di coercizione dei tipi impliciti
In ANSI_MODE = TRUEDatabricks SQL usa regole di cast del tipo di dati SQL non crittografate per:
Al contrario ANSI_MODE = FALSE , è incoerente e più leniente. Ad esempio:
- Quando si usa un
STRINGtipo con qualsiasi operatore aritmetico, la stringa viene implicitamente sottoposta a cast aDOUBLE. - Quando si confronta un oggetto
STRINGcon qualsiasi tipo numerico, viene eseguito il cast implicito della stringa al tipo a cui viene confrontato. - Quando si esegue un
UNION' ,COALESCEo altre operazioni in cui è necessario trovare un tipo meno comune, viene eseguito il cast diSTRINGtutti i tipi in se è presente unSTRINGtipo.
Databricks consiglia di usare il cast esplicito o la funzione try_cast anziché basarsi su ANSI_MODE = FALSE.
Esempi
> 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