ANSI_MODE
Van toepassing op:
Databricks SQL
De ANSI_MODE configuratieparameter bepaalt het belangrijkste gedrag van ingebouwde functies en cast-bewerkingen.
In dit artikel wordt de ANSI-modus in Databricks SQL beschreven. Voor informatie over ANSI-naleving in Databricks Runtime, zie ANSI-naleving in Databricks Runtime.
Instellingen
WAAR
Volgt de SQL-standaard in de manier waarop het gaat om bepaalde rekenkundige bewerkingen en typeconversies, vergelijkbaar met de meeste databases en datawarehouses. Het volgen van deze standaard bevordert een betere gegevenskwaliteit, integriteit en draagbaarheid.
ONWAAR
Databricks SQL maakt gebruik van hive-compatibel gedrag.
U kunt deze parameter instellen op sessieniveau met behulp van de SET-instructie en op globaal niveau met behulp van SQL-configuratieparameters of de SQL Warehouse-API-.
Standaardinstelling van het systeem
De standaardwaarde van het systeem is TRUE voor accounts die zijn toegevoegd aan Databricks SQL 2022.35 en hoger.
Gedetailleerde beschrijving
In de referentiedocumentatie voor Databricks SQL wordt het standaardgedrag van SQL beschreven.
In de volgende secties worden de verschillen tussen ANSI_MODETRUE (ANSI-modus) en FALSE (niet-ANSI-modus) beschreven.
Operatoren
In de niet-ANSI-modus kunnen rekenkundige bewerkingen die worden uitgevoerd op numerieke typen overloopwaarden of NULL retourneren, terwijl in de ANSI-modus dergelijke bewerkingen een fout retourneren.
| Bediener | Beschrijving | Voorbeeld | ANSI_MODE = waar | ANSI_MODE = onwaar |
|---|---|---|---|---|
| dividend / deler | Geeft als resultaat dividend gedeeld door deler. | 1/0 |
Fout | NULL |
| - expr | Retourneert de negatieve waarde van expr. | -(-128y) |
Fout |
-128y (Overflow) |
| expr1 - expr2 | Geeft het resultaat van de aftrekking van expressie2 van expressie1. | -128y - 1y |
Fout |
127y (Overloop) |
| expr1 + expr2 | Retourneert de som van expr1 en expr2. | 127y + 1y |
Fout |
-128y (Overloop) |
| deeltal % deler | Geeft het overblijfsel na deling van het deeltal door de deler. | 1 % 0 |
Fout | NULL |
| vermenigvuldiger * vermenigvuldiging | Retourneert het product van de vermenigvuldiger en het vermenigvuldigtal. | 100y * 100y |
Fout |
16y (Overloop) |
| arrayExpr[index] | Retourneert het element van een arrayExpr bij index. | Ongeldige array-index | Fout | NULL |
| mapExpr[key] | Retourneert de waarde van mapExpr voor sleutel. | Ongeldige kaartsleutel | Fout | NULL |
| deler deeldividend | Berekent het integraal deel van de verdeling van de deler door dividend. | 1 div 0 |
Fout | NULL |
Functies
Het gedrag van sommige ingebouwde functies kan verschillen onder ANSI-modus versus niet-ANSI-modus onder de onderstaande voorwaarden.
| Operator | Beschrijving | Voorwaarde | ANSI_MODE = waar | ANSI_MODE = onwaar |
|---|---|---|---|---|
| abs(expr) | Retourneert de absolute waarde van de numerieke waarde in expr. | abs(-128y) |
Fout |
-128y (Overflow) |
| element_at(mapExpr, sleutel) | Retourneert de waarde van mapExpr voor sleutel. | Ongeldige kaartsleutel | Fout | NULL |
| element_at(arrayExpr, index) | Retourneert het element van een arrayExpr bij index. | Ongeldige array-index | Fout | NULL |
| elt(index, expr1 [, ...] ) | Retourneert de nde expressie. | Ongeldige index | Fout | NULL |
| make_date(y,m,d) | Hiermee maakt u een datum op basis van jaar-, maand- en dagvelden. | Ongeldige resultaatdatum | Fout | NULL |
| make_timestamp(y,m,d,h,mi,s[,tz]) | Hiermee maakt u een tijdstempel op basis van velden. | Ongeldig resultaattijdstempel | Fout | NULL |
| make_interval(y,m,w,d,h,mi,s) | Hiermee maakt u een interval op basis van velden. | Ongeldig resultaatinterval | Fout | NULL |
| mod(deeltal, deler) | Retourneert de rest na het delen van het deeltal door de deler. | mod(1, 0) |
Fout | NULL |
| next_day(expr,dayOfWeek) | Retourneert de eerste datum die later is dan expr en benoemd als in dayOfWeek. | Ongeldige dag van de week | Fout | NULL |
| parse_url(URL, partToExtract[, key]) | Extraheert een deel uit de URL. | Ongeldige URL | Fout | NULL |
| pmod(dividend, deler) | Geeft de positieve rest na deling door deler. | pmod(1, 0) |
Error | NULL |
| size(expr) | Geeft de kardinaliteit van expr terug. | size(NULL) |
NULL |
-1 |
| to_date(expr[,fmt]) | Retourneert expr cast naar een datum met een optionele opmaak. | Ongeldige expressie- of formaattekenreeks | Fout | NULL |
| to_timestamp(expr[,fmt]) | Retourneert expr cast naar een tijdstempel met behulp van een optionele opmaak. | Ongeldige expressie- of opmaakreeks | Fout | NULL |
| to_unix_timestamp(expr[,fmt]) | Retourneert de tijdstempel in expr als een UNIX-tijdstempel. | Ongeldige expressie- of formaattekenreeks | Fout | NULL |
| unix_timestamp([expr[, fmt]]) | Retourneert de UNIX-tijdstempel van de huidige of opgegeven tijd. | Ongeldige uitdrukking- of opmaakreeks | Fout | NULL |
Regels voor het casten
De regels en gedragingen met betrekking tot CAST zijn strenger in de ANSI-modus. Ze kunnen worden onderverdeeld in de volgende drie categorieën:
Regels voor compilatietijdconversie
| Brontype | Doeltype | Voorbeeld | ANSI_MODE = waar | ANSI_MODE = onwaar |
|---|---|---|---|---|
| Booleaans | Tijdstempel | cast(TRUE AS TIMESTAMP) |
Fout | 1970-01-01 00:00:00.000001 UTC |
| Datum | Boolean | cast(DATE'2001-08-09' AS BOOLEAN) |
Fout | NULL |
| Tijdstempel | Booleaans | cast(TIMESTAMP'1970-01-01 00:00:00Z' AS BOOLEAN) |
Fout | FALSE |
| Integraal numeriek | Binair | cast(15 AS BINARY) |
Fout | binaire weergave |
Runtimefouten
| Brontype | Doeltype | Voorwaarde | Voorbeeld | ANSI_MODE = waar | ANSI_MODE = onwaar |
|---|---|---|---|---|---|
| String | Niet-tekenreeks | Ongeldige invoer | cast('a' AS INTEGER) |
Fout | NULL |
| Matrix, Struct, Kaart | Matrix, Struct, Kaart | Ongeldige invoer | cast(ARRAY('1','2','3') AS ARRAY<DATE>) |
Fout | NULL |
| Numeriek | Numeriek | Overloop | cast(12345 AS BYTE) |
Fout | NULL |
| Numeriek | Integraal numeriek | Truncatie | cast(5.1 AS INTEGER) |
Fout | 5 |
Notitie
Voor elk van deze casts kunt u try_cast gebruiken in plaats van cast om een NULL te retourneren in plaats van een fout.
Impliciete regels voor type coercion
Onder ANSI_MODE = TRUE, Databricks SQL maakt gebruik van duidelijke SQL-gegevenstype cast-regels voor:
Het is daarentegen ANSI_MODE = FALSE inconsistent en milder. Voorbeeld:
- Wanneer u een
STRINGtype gebruikt met een rekenkundige operator, wordt de tekenreeks impliciet omgezet inDOUBLE. - Wanneer u een
STRINGmet een numeriek type vergelijkt, wordt de tekenreeks impliciet omgezet in het type waarmee het wordt vergeleken. - Bij het uitvoeren van een
UNION,COALESCEof andere bewerkingen waarbij een minst gangbaar type moet worden gevonden, worden alle typen omgezet inSTRINGals erSTRINGtype aanwezig is.
Databricks raadt aan om de expliciete cast - of try_cast-functie te gebruiken in plaats van erop te ANSI_MODE = FALSEvertrouwen.
Voorbeelden
> 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