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

• SANN

  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.

• FALSK

  Databricks SQL använder Hive-kompatibelt beteende.

Du kan set den här parametern på sessionsnivå genom att använda SET-instruktionen och på global nivå genom att använda SQL-konfigurationen parameters eller via SQL Warehouse API.

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_MODETRUE (ANSI-läge) och FALSE (icke-ANSI-läge).

Operatorer

I icke-ANSI-läge kan aritmetiska operationer som utförs på numeriska typer returnera överskridna values eller NULL, medan sådana operationer 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
- uttr	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

Funktioner

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	Description	Villkor	ANSI_MODE = true	ANSI_MODE = falsk
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
• Körningsfel
• Regler för implicit typtvång

Kompilera tidskonverteringsregler

Source type	Måltyp	Exempel	ANSI_MODE = true	ANSI_MODE = falsk
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 = sant	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:

• typhöjning
• nedständande
• crosscasting

Däremot ANSI_MODE = FALSE är inkonsekvent och mildare. Till exempel:

• När du använder en STRING typ med en aritmetikoperator omvandlas strängen implicit till DOUBLE.
• När du jämför en STRING med någon numerisk typ omvandlas strängen implicit till den typ som den jämför med.
• När du utför UNION, COALESCEeller andra operationer where måste en gemensam typ hittas, och alla typer måste konverteras till STRING om det finns någon STRING-typ.

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

Relaterade

• REGLER FÖR SQL-datatyp
• RESET
• SET uttalande
• Konfigurera SQL parameters
• SQL Warehouse API