ANSI_MODE
Dotyczy:
Databricks SQL
Parametr ANSI_MODE konfiguracji steruje kluczowymi zachowaniami wbudowanych funkcji i operacji rzutowania.
W tym artykule opisano tryb ANSI w usłudze Databricks SQL. Aby uzyskać informacje o zgodności ANSI w środowisku Databricks Runtime, zobacz Zgodność ANSI w środowisku Databricks Runtime.
Ustawienia
PRAWDA
Jest zgodny ze standardem SQL w zakresie obsługi niektórych operacji arytmetycznych i konwersji typów, podobnie jak w przypadku większości baz danych i magazynów danych. Zgodnie z tym standardem zwiększa się jakość danych, integralność i przenośność.
FAŁSZ
Usługa SQL usługi Databricks używa zachowania zgodnego z programem Hive.
Ten parametr można ustawić na poziomie sesji przy użyciu instrukcji SET i na poziomie globalnym przy użyciu parametrów konfiguracji SQL lub interfejsu API usługi SQL Warehouse.
Ustawienie domyślne systemu
Wartość domyślna systemu dotyczy TRUE kont dodanych w usłudze Databricks SQL 2022.35 lub nowszym.
Szczegółowy opis
Dokumentacja referencyjna usługi Databricks SQL opisuje standardowe zachowanie języka SQL.
W poniższych sekcjach opisano różnice między trybem ANSI_MODE TRUE (tryb ANSI) i FALSE (tryb inny niż ANSI).
Operatory
W trybie innym niż ANSI operacje arytmetyczne wykonywane na typach liczbowych mogą zwracać przepełnione wartości lub wartość NULL, podczas gdy w trybie ANSI takie operacje zwracają błąd.
| Operator | opis | Przykład | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|
| dywidenda /dzielnika | Zwraca dywidendę podzieloną przez dzielnik. | 1/0 |
Błąd | NULL |
| -Expr | Zwraca negowaną wartość expr. | -(-128y) |
Błąd | -128y (Przepełnienie) |
| expr1 — wyrażenie2 | Zwraca odejmowanie wyrażenie2 z wyrażenie1. | -128y - 1y |
Błąd | 127y (Przepełnienie) |
| expr1 + expr2 | Zwraca sumę wyrażenie1 i wyrażenie2. | 127y + 1y |
Błąd | -128y (Przepełnienie) |
| dzielnika w % dywidendy | Zwraca resztę po dywidendzie / dzielnika. | 1 % 0 |
Błąd | NULL |
| mnożnik * mnożnik * mnożenie | Zwraca mnożnik mnożony przez mnożenie wielokropka. | 100y * 100y |
Błąd | 16y (Przepełnienie) |
| arrayExpr[index] | Zwraca element tablicyExpr w indeksie. | Nieprawidłowy indeks tablicy | Błąd | NULL |
| mapExpr[key] | Zwraca wartość mapExpr dla klucza. | Nieprawidłowy klucz mapy | Błąd | NULL |
| dywidenda divisor div | Zwraca integralną część podziału dzielnika przez dywidendę. | 1 div 0 |
Błąd | NULL |
Funkcje
Zachowanie niektórych wbudowanych funkcji może się różnić w trybie ANSI a w trybie innym niż ANSI w warunkach określonych poniżej.
| Operator | opis | Warunek | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|
| abs(wyrażenie) | Zwraca wartość bezwzględną wartości liczbowej w eksplorze. | abs(-128y) |
Błąd | -128y (Przepełnienie) |
| element_at(mapExpr, klucz) | Zwraca wartość mapExpr dla klucza. | Nieprawidłowy klucz mapy | Błąd | NULL |
| element_at(arrayExpr, index) | Zwraca element tablicyExpr w indeksie. | Nieprawidłowy indeks tablicy | Błąd | NULL |
| elt(index, expr1 [, ...] ) | Zwraca wyrażenie nth. | Nieprawidłowy indeks | Błąd | NULL |
| make_date(y,m,d) | Tworzy datę z pól rok, miesiąc i dzień. | Nieprawidłowa data wyniku | Błąd | NULL |
| make_timestamp(y,m,d,h,mi,s[,tz]) | Tworzy znacznik czasu na podstawie pól. | Nieprawidłowy znacznik czasu wyniku | Błąd | NULL |
| make_interval(y,m,w,d,h,mi,s) | Tworzy interwał z pól. | Nieprawidłowy interwał wyników | Błąd | NULL |
| mod(dywidenda, dzielnica) | Zwraca resztę po dywidendzie / dzielnika. | mod(1, 0) |
Błąd | NULL |
| next_day(wyrażenie,dayOfWeek) | Zwraca pierwszą datę późniejszą niż wyrażenie i nazwaną jako w dayOfWeek. | Nieprawidłowy dzień tygodnia | Błąd | NULL |
| parse_url(url, partToExtract[, key]) | Wyodrębnia część z adresu URL. | Nieprawidłowy adres URL | Błąd | NULL |
| pmod(dywidenda, dzielnica) | Zwraca dodatnią resztę po dywidendzie / dzielnika. | pmod(1, 0) |
Błąd | NULL |
| size(expr) | Zwraca kardynalność expr. | size(NULL) |
NULL |
-1 |
| to_date(wyrażenie[,fmt]) | Zwraca wyrażenie rzutowania do daty przy użyciu opcjonalnego formatowania. | Nieprawidłowy wyrażenie lub ciąg formatu | Błąd | NULL |
| to_timestamp(wyrażenie[,fmt]) | Zwraca rzutowanie eksplor do znacznika czasu przy użyciu opcjonalnego formatowania. | Nieprawidłowy wyrażenie lub ciąg formatu | Błąd | NULL |
| to_unix_timestamp(wyrażenie[,fmt]) | Zwraca znacznik czasu w eksplorze jako sygnaturę czasową system UNIX. | Nieprawidłowy wyrażenie lub ciąg formatu | Błąd | NULL |
| unix_timestamp([wyrażenie[, fmt]]) | Zwraca znacznik czasu system UNIX bieżącego lub określonego czasu. | Nieprawidłowy wyrażenie lub ciąg formatu | Błąd | NULL |
Reguły rzutów
Reguły i zachowania dotyczące funkcji CAST są bardziej rygorystyczne w trybie ANSI. Można je podzielić na następujące trzy kategorie:
- Reguły konwersji w czasie kompilacji
- Błędy środowiska uruchomieniowego
- Reguły wymuszania typów niejawnych
Reguły konwersji w czasie kompilacji
| Source type | Typ docelowy | Przykład | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|
| Wartość logiczna | Sygnatura czasowa | cast(TRUE AS TIMESTAMP) |
Błąd | 1970-01-01 00:00:00.000001 UTC |
| Data | Wartość logiczna | cast(DATE'2001-08-09' AS BOOLEAN) |
Błąd | NULL |
| Sygnatura czasowa | Wartość logiczna | cast(TIMESTAMP'1970-01-01 00:00:00Z' AS BOOLEAN) |
Błąd | FALSE |
| Liczba całkowita | Plik binarny | cast(15 AS BINARY) |
Błąd | reprezentacja binarna |
Błędy środowiska uruchomieniowego
| Source type | Typ docelowy | Stan | Przykład | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|---|
| String | Ciąg inny niż ciąg | Nieprawidłowe dane wejściowe | cast('a' AS INTEGER) |
Błąd | NULL |
| Array, Struct, Map | Array, Struct, Map | Nieprawidłowe dane wejściowe | cast(ARRAY('1','2','3') AS ARRAY<DATE>) |
Błąd | NULL |
| Liczbowe | Liczbowe | Przepełnienie | cast(12345 AS BYTE) |
Błąd | NULL |
| Liczbowe | Liczba całkowita | Obcinania | cast(5.1 AS INTEGER) |
Błąd | 5 |
Uwaga
Dla każdego z tych rzutów można użyć try_cast zamiast rzutowania, aby powrócićNULL, a nie błędu.
Reguły wymuszania typów niejawnych
W obszarze ANSI_MODE = TRUEusługa SQL usługi Databricks używa reguł rzutywania typów danych JĘZYKA SQL dla:
ANSI_MODE = FALSE Natomiast jest niespójny i bardziej łagodny. Na przykład:
- W przypadku używania typu z dowolnym operatorem
STRINGarytmetycznym ciąg jest niejawnie rzutowy naDOUBLE. - Podczas porównywania wartości z dowolnym typem
STRINGliczbowym ciąg jest niejawnie rzutowy do typu, z którego jest porównywany. - Podczas wykonywania
UNIONoperacji ,COALESCElub innych, w których należy odnaleźć najmniej wspólny typ, wszystkie typy są rzutowane,STRINGjeśli istnieje jakikolwiekSTRINGtyp.
Usługa Databricks zaleca używanie jawnej funkcji rzutowania lub try_cast zamiast polegać na funkcji ANSI_MODE = FALSE.
Przykłady
> 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