Megosztás a következőn keresztül:

ANSI_MODE

  • Cikk

A következőkre vonatkozik: jelölje be az igennel jelölt jelölőnégyzetet Databricks SQL

A ANSI_MODE konfigurációs paraméter szabályozza a beépített függvények és az öntvényműveletek fő viselkedését.

Ez a cikk a Databricks SQL ANSI-módját ismerteti. A Databricks Runtime ANSI-megfelelőségével kapcsolatban lásd a Databricks Runtime ANSI-megfelelőségét.

Beállítások

  • IGAZ

    A legtöbb adatbázishoz és adattárházhoz hasonlóan az SQL-szabványt követi, hogyan kezeli bizonyos aritmetikai műveleteket és típuskonverziókat. Ennek a szabványnak a betartása jobb adatminőséget, integritást és hordozhatóságot biztosít.

  • TÉVES

    A Databricks SQL Hive-kompatibilis viselkedést használ.

Ezt a paramétert a munkamenet szintjén a SET utasítással, globális szinten pedig SQL-konfigurációs paraméterekkel vagy az SQL Warehouse API-val állíthatja be.

A rendszer alapértelmezése

A rendszer alapértelmezett értéke a TRUE Databricks SQL 2022.35-ös vagy újabb verzióban hozzáadott fiókokhoz tartozik.

Részletes leírás

A Databricks SQL referenciadokumentációja az SQL standard viselkedését ismerteti.

A következő szakaszok a ANSI_MODE TRUE (ANSI mód) és FALSE a (nem ANSI-mód) közötti különbségeket ismertetik.

Operátorok

Nem ANSI módban a numerikus típusokon végrehajtott számtani műveletek túlcsordult értékeket vagy NULL értéket adhatnak vissza, míg ANSI módban az ilyen műveletek hibát adnak vissza.

Operátor Leírás Példa ANSI_MODE = igaz ANSI_MODE = hamis
osztalék/ osztó Osztóval osztva az osztalékot adja eredményül. 1/0 Hiba NULL
- kifésül Az expr negatív értékét adja eredményül. -(-128y) Hiba -128y (Túlcsordulás)
expr1 - expr2 Az expr2 kivonását adja vissza az expr1-ből. -128y - 1y Hiba 127y (Túlcsordulás)
expr1 + expr2 Az expr1 és a kitevő2 összegét adja vissza. 127y + 1y Hiba -128y (Túlcsordulás)
osztalék %-os osztója Az osztalék/ osztó után fennmaradó részt adja eredményül. 1 % 0 Hiba NULL
szorzó * szorzás Szorzó szorzását adja eredményül. 100y * 100y Hiba 16y (Túlcsordulás)
arrayExpr[index] Egy tömbExpr elemét adja vissza az indexben. Érvénytelen tömbindex Hiba NULL
mapExpr[key] A mapExpr kulcs értékét adja vissza. Érvénytelen térképkulcs Hiba NULL
osztó div osztalék Az osztó osztalékkal való megosztásának szerves részét adja vissza. 1 div 0 Hiba NULL

Függvények

Egyes beépített függvények viselkedése eltérő lehet ANSI módban és nem ANSI módban az alábbi feltételek mellett.

Operátor Leírás Feltétel ANSI_MODE = igaz ANSI_MODE = hamis
abs(expr) Az expr numerikus értékének abszolút értékét adja vissza. abs(-128y) Hiba -128y (Túlcsordulás)
element_at(mapExpr, kulcs) A mapExpr kulcs értékét adja vissza. Érvénytelen térképkulcs Hiba NULL
element_at(arrayExpr, index) Egy tömbExpr elemét adja vissza az indexben. Érvénytelen tömbindex Hiba NULL
elt(index, expr1 [, ...] ) Az n. kifejezést adja vissza. Érvénytelen index Hiba NULL
make_date(y;m;d) Dátumot hoz létre az év, a hónap és a nap mezőiből. Érvénytelen eredménydátum Hiba NULL
make_timestamp(y,m,d,h,mi;s[,tz]) Időbélyeget hoz létre a mezőkből. Érvénytelen eredményidőbélyegző Hiba NULL
make_interval(y,m,w,d,h;mi;s) Intervallumot hoz létre a mezőkből. Érvénytelen eredményintervallum Hiba NULL
mod(osztalék, osztó) Az osztalék/ osztó után fennmaradó részt adja eredményül. mod(1, 0) Hiba NULL
next_day(expr;dayOfWeek) Az első dátumot adja vissza, amely későbbi, mint az expr, és a dayOfWeek névvel van elnevezve. Érvénytelen a hét napja Hiba NULL
parse_url(url, partToExtract[, key]) Kinyer egy részt az URL-címből. Érvénytelen URL-cím Hiba NULL
pmod(osztalék, osztó) Az osztalék/ osztó után a pozitív maradékot adja eredményül. pmod(1, 0) Hiba NULL
size(expr) Az expr számosságát adja vissza. size(NULL) NULL -1
to_date(expr[;fmt]) Az expr cast értéket adja vissza egy dátumra opcionális formázással. Érvénytelen kifejezés vagy formázási sztring Hiba NULL
to_timestamp(expr[;fmt]) A kitevőt egy időbélyegre adja vissza opcionális formázással. Érvénytelen kifejezés vagy formázási sztring Hiba NULL
to_unix_timestamp(expr[;fmt]) UnIX-időbélyegként adja vissza az időbélyeget a kitérőben. Érvénytelen kifejezés vagy formázási sztring Hiba NULL
unix_timestamp([expr[, fmt]]) Az aktuális vagy megadott idő UNIX időbélyegét adja vissza. Érvénytelen kifejezés vagy formázási sztring Hiba NULL

Kiosztási szabályok

A CAST-hez kapcsolódó szabályok és viselkedések ANSI módban szigorúbbak. Ezek három kategóriába sorolhatók:

Fordítási idő konverziós szabályai

Forrás típusa Céltípus Példa ANSI_MODE = igaz ANSI_MODE = hamis
Logikai Időbélyegző cast(TRUE AS TIMESTAMP) Hiba 1970-01-01 00:00:00.000001 UTC
Dátum Logikai cast(DATE'2001-08-09' AS BOOLEAN) Hiba NULL
Időbélyegző Logikai cast(TIMESTAMP'1970-01-01 00:00:00Z' AS BOOLEAN) Hiba FALSE
Integrál numerikus Bináris cast(15 AS BINARY) Hiba bináris ábrázolás

Futtatókörnyezeti hibák

Forrás típusa Céltípus Feltétel Példa ANSI_MODE = igaz ANSI_MODE = hamis
Sztring Nem sztring Érvénytelen bemenet cast('a' AS INTEGER) Hiba NULL
Tömb, Struct, Térkép Tömb, Struct, Térkép Érvénytelen bemenet cast(ARRAY('1','2','3') AS ARRAY<DATE>) Hiba NULL
Numerikus Numerikus Túlcsordulás cast(12345 AS BYTE) Hiba NULL
Numerikus Integrál numerikus Csonkolás cast(5.1 AS INTEGER) Hiba 5

Feljegyzés

Mindegyik szereposztáshoz használhatja a try_cast ahelyett , hogy hiba helyett visszatérést NULL eredményez.

Implicit típusú kényszerítő szabályok

A Databricks SQL a ANSI_MODE = TRUEkövetkező célokra használ egyértelmű SQL-adattípus-öntési szabályokat :

Ezzel szemben ANSI_MODE = FALSE inkonzisztens és engedékenyebb. Példa:

  • Ha bármilyen aritmetikai operátorral rendelkező típust STRING használ, a sztring implicit módon a következőre DOUBLElesz öntött.
  • Ha bármilyen numerikus típussal összehasonlít egy sztringet STRING , a sztring implicit módon ahhoz a típushoz lesz öntött, amelyet összehasonlít.
  • Olyan , COALESCEvagy egyéb műveletek végrehajtásakorUNION, amelyeknél a legkevésbé gyakori típust kell megtalálni, az összes típust át kell adniSTRING, ha van ilyen STRING típus.

A Databricks az explicit cast vagy try_cast függvény használatát javasolja ahelyett, hogy a függvényre támaszkodik ANSI_MODE = FALSE.

Példák

> 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