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. Zie ANSI-naleving in Databricks Runtime voor 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.

Systeemstandaard

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_MODE TRUE (ANSI-modus) en FALSE (niet-ANSI-modus) beschreven.

Operators

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.

Operator	Description	Voorbeeld	ANSI_MODE = true	ANSI_MODE = onwaar
dividend/deler	Geeft als resultaat dividend gedeeld door deler.	1/0	Error	NULL
- expr	Retourneert de negatieve waarde van expr.	-(-128y)	Error	-128y (Overloop)
expr1 - expr2	Retourneert de aftrekking van expr2 van expr1.	-128y - 1y	Error	127y (Overloop)
expr1 + expr2	Retourneert de som van expr1 en expr2.	127y + 1y	Error	-128y (Overloop)
dividendpercentage deler	Retourneert de rest na dividend/deler.	1 % 0	Error	NULL
vermenigvuldiger * vermenigvuldiging	Retourneert vermenigvuldiger met vermenigvuldiging met vermenigvuldiging.	100y * 100y	Error	16y (Overloop)
arrayExpr[index]	Retourneert het element van een arrayExpr bij index.	Ongeldige matrixindex	Error	NULL
mapExpr[key]	Retourneert de waarde van mapExpr voor sleutel.	Ongeldige kaartsleutel	Error	NULL
deeldividend deler	Berekent het integraal deel van de verdeling van de deler door dividend.	1 div 0	Error	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 = true	ANSI_MODE = onwaar
abs(expr)	Retourneert de absolute waarde van de numerieke waarde in expr.	abs(-128y)	Error	-128y (Overloop)
element_at(mapExpr, sleutel)	Retourneert de waarde van mapExpr voor sleutel.	Ongeldige kaartsleutel	Error	NULL
element_at(arrayExpr, index)	Retourneert het element van een arrayExpr bij index.	Ongeldige matrixindex	Error	NULL
elt(index, expr1 [, ...] )	Retourneert de nde expressie.	Ongeldige index	Error	NULL
make_date(y,m,d)	Hiermee maakt u een datum op basis van jaar-, maand- en dagvelden.	Ongeldige resultaatdatum	Error	NULL
make_timestamp(y,m,d,h,mi,s[,tz])	Hiermee maakt u een tijdstempel op basis van velden.	Ongeldig resultaattijdstempel	Error	NULL
make_interval(y,m,w,d,h,mi,s)	Hiermee maakt u een interval op basis van velden.	Ongeldig resultaatinterval	Error	NULL
mod(dividend, deler)	Retourneert de rest na dividend/deler.	mod(1, 0)	Error	NULL
next_day(expr,dayOfWeek)	Retourneert de eerste datum die later is dan expr en benoemd als in dayOfWeek.	Ongeldige dag van de week	Error	NULL
parse_url(URL, partToExtract[, key])	Extraheert een deel uit de URL.	Ongeldige URL	Error	NULL
pmod(dividend, deler)	Berekent de positieve rest na dividend/deler.	pmod(1, 0)	Error	NULL
size(expr)	Geeft als resultaat de kardinaliteit van expr.	size(NULL)	NULL	-1
to_date(expr[,fmt])	Retourneert expr cast naar een datum met een optionele opmaak.	Ongeldige expr- of notatietekenreeks	Error	NULL
to_timestamp(expr[,fmt])	Retourneert expr cast naar een tijdstempel met behulp van een optionele opmaak.	Ongeldige expr- of notatietekenreeks	Error	NULL
to_unix_timestamp(expr[,fmt])	Retourneert de tijdstempel in expr als een UNIX-tijdstempel.	Ongeldige expr- of notatietekenreeks	Error	NULL
unix_timestamp([expr[, fmt]])	Retourneert de UNIX-tijdstempel van de huidige of opgegeven tijd.	Ongeldige expr- of notatietekenreeks	Error	NULL

Regels voor 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
• Runtimefouten
• Impliciete regels voor type coercion

Regels voor compilatietijdconversie

Brontype	Doeltype	Opmerking	ANSI_MODE = true	ANSI_MODE = onwaar
Booleaanse waarde	Tijdstempel	cast(TRUE AS TIMESTAMP)	Error	1970-01-01 00:00:00.000001 UTC
Datum	Booleaanse waarde	cast(DATE'2001-08-09' AS BOOLEAN)	Error	NULL
Tijdstempel	Booleaanse waarde	cast(TIMESTAMP'1970-01-01 00:00:00Z' AS BOOLEAN)	Error	FALSE
Integraal numeriek	Binary	cast(15 AS BINARY)	Error	binaire weergave

Runtimefouten

Brontype	Doeltype	Conditie	Opmerking	ANSI_MODE = true	ANSI_MODE = onwaar
String	Niet-tekenreeks	Ongeldige invoer	cast('a' AS INTEGER)	Error	NULL
Matrix, Struct, Kaart	Matrix, Struct, Kaart	Ongeldige invoer	cast(ARRAY('1','2','3') AS ARRAY<DATE>)	Error	NULL
Numeriek	Numeriek	Overloop	cast(12345 AS BYTE)	Error	NULL
Numeriek	Integraal numeriek	Truncatie	cast(5.1 AS INTEGER)	Error	5

Notitie

Voor elk van deze casts kunt u try_cast gebruiken in plaats van cast om te retourneren NULL 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:

• type promotie
• downcasting
• crosscasting

Het is daarentegen ANSI_MODE = FALSE inconsistent en lener. Voorbeeld:

• Wanneer u een type met een STRING rekenkundige operator gebruikt, wordt de tekenreeks impliciet omgezet in DOUBLE.
• Wanneer u een getal vergelijkt met een STRING numeriek type, wordt de tekenreeks impliciet omgezet in het type dat wordt vergeleken met.
• Bij het uitvoeren van een UNION, COALESCEof andere bewerkingen waarbij een minst gangbaar type moet worden gevonden, worden alle typen gecast naar STRING als er een STRING type 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

Gerelateerd

• REGELS voor SQL-gegevenstypen
• RESET
• SET-instructie
• SQL-parameters configureren
• SQL Warehouse-API