ANSI_MODE

  • Artikel

Gilt für:check marked yes Databricks SQL

Der ANSI_MODE-Konfigurationsparameter steuert wichtige Verhaltensweisen integrierter Funktionen und Umwandlungsvorgänge.

In diesem Artikel wird der ANSI-Modus in Databricks SQL beschrieben. Informationen zur ANSI-Konformität in Databricks Runtime finden Sie unter ANSI-Kompatibilität in Databricks Runtime.

Einstellungen

  • TRUE

    Folgt dem SQL Standard, wie es sich mit bestimmten arithmetischen Vorgängen und Typkonvertierungen befasst, ähnlich wie bei den meisten Datenbanken und Data Warehouses. Nach diesem Standard wird eine bessere Datenqualität, Integrität und Portabilität gefördert.

  • FALSE

    Databricks SQL verwendet Hive-kompatibles Verhalten.

Sie können diesen Parameter auf Sitzungsebene mithilfe der SET-Anweisung und auf globaler Ebene mithilfe von SQL-Konfigurationsparametern oder mithilfe der SQL-Warehouse-API festlegen.

Systemstandard

Der Systemstandardwert lautet „TRUE“ für Konten, die vor Databricks SQL 2022.35 oder höher hinzugefügt wurden.

Detaillierte Beschreibung

Die Databricks SQL Referenzdokumentation beschreibt SQL Standardverhalten.

In den folgenden Abschnitten werden die Unterschiede zwischen ANSI_MODE TRUE(ANSI-Modus) und FALSE (nicht ANSI-Modus) beschrieben.

Operatoren

Im Nicht-ANSI-Modus können arithmetische Vorgänge, die auf numerischen Typen ausgeführt werden, überlaufende Werte oder NULL zurückgeben, während im ANSI-Modus solche Vorgänge einen Fehler zurückgeben.

Operator Beschreibung Beispiel ANSI_MODE = true ANSI_MODE = false
dividend / divisor Gibt Dividende dividiert durch Divisor. 1/0 Fehler NULL
- expr Gibt den negierten Wert des Ausdrucks. zurück. -(-128y) Fehler -128y (Überlauf)
expr1 - expr2 Gibt die Subtraktion des Ausdrucks2 aus Ausdrucks1 zurück. -128y - 1y Fehler 127y (Überlauf)
expr1 + expr2 Gibt die Summe des Ausdrucks1 und des Ausdrucks2 zurück. 127y + 1y Fehler -128y (Überlauf)
dividend % divisor Gibt den Rest nach Dividenden / Divisor zurück. 1 % 0 Fehler NULL
multiplier * multiplicand Gibt Multiplikator multipliziert mit Multiplikanten zurück. 100y * 100y Fehler 16y (Überlauf)
arrayExpr[index] Gibt das Element eines ArrayExpr beim Index zurück. Ungültiger Array-Index Fehler NULL
mapExpr[key] Gibt den Wert von mapExpr für Schlüssel zurück. Ungültiger Kartenschlüssel Fehler NULL
divisor div dividend Gibt den ganzzahligen Teil der Division vom Divisor durch die Dividende zurück. 1 div 0 Fehler NULL

Functions

Das Verhalten einiger integrierter Funktionen kann im ANSI-Modus und im Nicht-ANSI-Modus unter den unten angegebenen Bedingungen unterschiedlich sein.

Operator Beschreibung Bedingung ANSI_MODE = true ANSI_MODE = false
abs(expr) Gibt den absoluten Wert des numerischen Werts im Ausdruck zurück. abs(-128y) Fehler -128y (Überlauf)
element_at(mapExpr, key) Gibt den Wert von mapExpr für Schlüssel zurück. Ungültiger Kartenschlüssel Fehler NULL
element_at(arrayExpr, index) Gibt das Element eines ArrayExpr beim Index zurück. Ungültiger Array-Index Fehler NULL
elt(index, expr1 [, …] ) Gibt den n-ten Ausdruck zurück. Ungültiger Index Fehler NULL
make_date(y;m;d) Erstellt ein Datum auf der Grundlage der Felder Jahr, Monat und Tag. Ungültiges Ergebnisdatum Fehler NULL
make_timestamp(y,m,d,h,mi,s[,tz]) Erstellt einen Zeitstempel aus Feldern. Ungültiger Zeitstempel des Ergebnisses Fehler NULL
make_interval(y;m;w;d,h,mi,s) Erstellt ein Intervall aus Feldern. Ungültiges Ergebnisintervall Fehler NULL
mod(dividend, divisor) Gibt den Rest nach Dividenden / Divisor zurück. mod(1, 0) Fehler NULL
next_day(expr,dayOfWeek) Gibt das erste Datum zurück, das nach dem Ausdruck liegt und wie in dayOfWeek. benannt ist. Ungültiger Tag der Woche Fehler NULL
parse_url(url, partToExtract[, key]) Extrahiert einen Teil der URL. Ungültige URL Fehler NULL
pmod(dividend, divisor) Gibt die positive Rücksendung nach Dividenden / Divisor zurück. pmod(1, 0) Fehler NULL
size(expr) Gibt die Kardinalität des Ausdrucks zurück. size(NULL) NULL -1
to_date(expr[,fmt]) Gibt den Ausdruck umgewandelt in ein Datum unter Verwendung einer optionalen Formatierung zurück. Ungültige Ausdrucks- oder Formatzeichenfolge Fehler NULL
to_timestamp(expr[,fmt]) Gibt den Ausdruck umgewandelt in einen Zeitstempel unter Verwendung einer optionalen Formatierung zurück. Ungültige Ausdrucks- oder Formatzeichenfolge Fehler NULL
to_unix_timestamp(expr[,fmt]) Gibt den Zeitstempel im Ausdruck als UNIX-Zeitstempel zurück. Ungültige Ausdrucks- oder Formatzeichenfolge Fehler NULL
unix_timestamp([expr[, fmt]]) Gibt den UNIX-Zeitstempel der aktuellen oder angegebenen Zeit zurück. Ungültige Ausdrucks- oder Formatzeichenfolge Fehler NULL

Umwandlungsregeln

Die Regeln und Verhaltensweisen in Bezug auf CAST sind im ANSI-Modus strenger. Sie lassen sich grob in die folgenden drei Kategorien einteilen:

Kompilierungszeitkonvertierungsregeln

Quelltyp Zieltyp Beispiel ANSI_MODE = true ANSI_MODE = false
Boolean Timestamp cast(TRUE AS TIMESTAMP) Fehler 1970-01-01 00:00:00.000001 UTC
Date Boolean cast(DATE'2001-08-09' AS BOOLEAN) Fehler NULL
Timestamp Boolean cast(TIMESTAMP'1970-01-01 00:00:00Z' AS BOOLEAN) Fehler FALSE
Integral numerisch Binary cast(15 AS BINARY) Fehler Binäre Darstellung

Laufzeitfehler

Quelltyp Zieltyp Bedingung Beispiel ANSI_MODE = true ANSI_MODE = false
String Keine Zeichenfolge Ungültige Eingabe cast('a' AS INTEGER) Fehler NULL
Array, Struktur, Zuordnung Array, Struktur, Zuordnung Ungültige Eingabe cast(ARRAY('1','2','3') AS ARRAY<DATE>) Fehler NULL
Numerisch Numerisch Überlauf cast(12345 AS BYTE) Fehler NULL
Numeric Integral numerisch Abschneiden cast(5.1 AS INTEGER) Fehler 5

Hinweis

Für jede dieser Umwandlungen können Sie try_cast anstelle einer Umwandlung verwenden, statt einen Fehler zurückzugeben NULL.

Implizite Typkoersionregeln

Unter ANSI_MODE = TRUE, Databricks SQL verwendet klare SQL Datentyp-Umwandlungsregeln für:

Im Gegensatz dazu ist ANSI_MODE = FALSE inkonsistent und länglicher. Beispiele:

  • Wenn Sie einen STRING-Typ mit einem beliebigen arithmetischen Operator verwenden, wird die Zeichenfolge implizit in DOUBLE umgewandelt.
  • Beim Vergleich eines STRING mit irgendeinem numerischen Typs wird die Zeichenfolge implizit in den typ umgewandelt, mit dem sie verglichen wird.
  • Bei der Ausführung eines UNION, COALESCE oder anderer Vorgänge, in denen ein am wenigsten gängiger Typ gefunden werden muss, werden alle Typen in den Fall umgerechnet STRING, wenn irgendein STRING-Typ vorhanden ist.

Databricks empfiehlt, die explizite Umwandlungs- oder try_cast-Funktion zu verwenden, anstatt auf ANSI_MODE = FALSE zu vertrauen.

Beispiele

> 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