Anmerkung
Der Zugriff auf diese Seite erfordert eine Genehmigung. Du kannst versuchen, dich anzumelden oder die Verzeichnisse zu wechseln.
Der Zugriff auf diese Seite erfordert eine Genehmigung. Du kannst versuchen , die Verzeichnisse zu wechseln.
Gilt für:
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
STIMMT
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.
FALSCH
Databricks SQL verwendet Hive-kompatibles Verhalten.
Sie können diesen Parameter auf Sitzungsebene mithilfe der SET-Anweisung und auf globaler Ebene mit SQL-Konfigurationsparametern oder der SQL Warehouse-API-festlegen.
System-Standardeinstellung
Für Konten, die am oder nach dem 19. Oktober 2022 erstellt wurden, ist TRUEder Systemstandard .
Detaillierte Beschreibung
Die Databricks SQL Referenzdokumentation beschreibt SQL Standardverhalten.
In den folgenden Abschnitten werden die Unterschiede zwischen ANSI_MODETRUE (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.
| Bediener | Beschreibung | Beispiel | ANSI_MODE = wahr | ANSI_MODE = falsch |
|---|---|---|---|---|
| dividend/divisor | Gibt das Ergebnis der Division der Dividende durch den Divisor zurück. | 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 von expr1 und expr2 zurück. | 127y + 1y |
Fehler |
-128y (Überlauf) |
| Dividende % Divisor | Gibt den Rest nach Dividenden / Divisor zurück. | 1 % 0 |
Fehler | NULL |
| Multiplikator * Multiplikatand | 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[Schlüssel] | Gibt den Wert von mapExpr für Schlüssel zurück. | Ungültiger Kartenschlüssel | Fehler | NULL |
| Divisor Div-Dividende | Gibt den ganzzahligen Teil der Division vom Divisor durch die Dividende zurück. | 1 div 0 |
Fehler | NULL |
Funktionen
Das Verhalten einiger integrierter Funktionen kann im ANSI-Modus und im Nicht-ANSI-Modus unter den unten angegebenen Bedingungen unterschiedlich sein.
| Bediener | Beschreibung | Bedingung | ANSI_MODE = wahr | ANSI_MODE = falsch |
|---|---|---|---|---|
| ABS (Ausdruck) | Gibt den absoluten Wert des numerischen Werts im Ausdruck zurück. | abs(-128y) |
Fehler |
-128y (Überlauf) |
| element_at(mapExpr, Schlüssel) | 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, Ausdruck1 [, ...] ) | 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(Dividende, Teiler) | Gibt den Rest nach Dividenden / Divisor zurück. | mod(1, 0) |
Fehler | NULL |
| next_day(expr,TagDerWoche) | 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(Dividende, Divisor) | Gibt die positive Rücksendung nach Dividenden / Divisor zurück. | pmod(1, 0) |
Fehler | NULL |
| Größe(Ausdruck) | 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(Ausdruck[;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(Ausdruck[,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 = wahr | ANSI_MODE = falsch |
|---|---|---|---|---|
| Boolescher Typ (Boolean) | Zeitstempel | cast(TRUE AS TIMESTAMP) |
Fehler | 1970-01-01 00:00:00.000001 UTC |
| Datum | Boolescher Typ (Boolean) | cast(DATE'2001-08-09' AS BOOLEAN) |
Fehler | NULL |
| Zeitstempel | Boolescher Typ (Boolean) | cast(TIMESTAMP'1970-01-01 00:00:00Z' AS BOOLEAN) |
Fehler | FALSE |
| Integral numerisch | Binär | cast(15 AS BINARY) |
Fehler | Binäre Darstellung |
Laufzeitfehler
| Quelltyp | Zieltyp | Bedingung | Beispiel | ANSI_MODE = wahr | ANSI_MODE = falsch |
|---|---|---|---|---|---|
| Schnur | 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 |
| Numerisch | 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 nachgiebiger. Beispiele:
- Wenn Sie einen
STRING-Typ mit einem beliebigen arithmetischen Operator verwenden, wird die Zeichenfolge implizit inDOUBLEumgewandelt. - Beim Vergleich eines
STRINGmit einem numerischen Typ wird die Zeichenfolge implizit in den Typ umgewandelt, mit dem sie verglichen wird. - Bei der Durchführung eines
UNION,COALESCEoder anderer Operationen, bei denen ein am wenigsten gemeinsamer Typ gefunden werden muss, werden alle Typen inSTRINGumgewandelt, falls einSTRING-Typ vorhanden ist.
Databricks empfiehlt, die explizite cast- oder try_cast-Funktion zu verwenden, anstatt sich auf ANSI_MODE = FALSE zu verlassen.
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
Verwandte Themen
- SQL-Datentypregeln
- RESET
- SET-Anweisung
- Konfigurieren von SQL-Parametern
- SQL-Warehouse-API