ANSI_MODE

Berlaku untuk:centang ditandai ya Databricks SQL

Parameter konfigurasi ANSI_MODE mengontrol perilaku utama fungsi bawaan dan operasi transmisi.

Artikel ini menjelaskan mode ANSI di Databricks SQL. Untuk kepatuhan ANSI di Databricks Runtime, lihat Kepatuhan ANSI di Databricks Runtime.

Pengaturan

  • BENAR

    Mengikuti standar SQL dalam cara menangani operasi aritmatika dan konversi jenis tertentu, mirip dengan sebagian besar database dan gudang data. Mengikuti standar ini mempromosikan kualitas data, integritas, dan portabilitas yang lebih baik.

  • PALSU

    Databricks SQL menggunakan perilaku yang kompatibel dengan Hive.

Anda dapat mengatur parameter ini di tingkat sesi menggunakan pernyataan SET dan di tingkat global menggunakan parameter konfigurasi SQL atau SQL Warehouse API.

Sistem bawaan

Untuk akun yang dibuat pada atau setelah 19 Oktober 2022, default sistem adalah TRUE.

Deskripsi terperinci

Dokumentasi referensi Databricks SQL menjelaskan perilaku standar SQL.

Bagian berikut menjelaskan perbedaan antara ANSI_MODETRUE (mode ANSI) dan FALSE (mode non-ANSI).

Operator

Dalam mode non-ANSI, operasi aritmatika yang dilakukan pada jenis numerik dapat mengembalikan nilai luapan atau NULL, sementara dalam mode ANSI operasi tersebut mengembalikan kesalahan.

Pengoperasi Deskripsi Contoh ANSI_MODE = benar ANSI_MODE = salah
pembilang / penyebut Mengembalikan dividen yang dibagi dengan pembagi. 1/0 Kesalahan NULL
- expr Menampilkan nilai negatif dari expr. -(-128y) Kesalahan -128y (Luapan)
expr1 - expr2 Mengembalikan pengurangan expr2 dari expr1. -128y - 1y Kesalahan 127y (Luapan)
expr1 + expr2 Mengembalikan jumlah expr1 dan expr2. 127y + 1y Kesalahan -128y (Luapan)
dividen % pembagi Mengembalikan sisanya setelah dividen/divisor. 1 % 0 Kesalahan NULL
pengali * pembilang Mengembalikan pengali dikalikan dengan multiplicand. 100y * 100y Kesalahan 16y (Luapan)
arrayExpr[indeks] Mengembalikan elemen arrayExpr pada indeks. Indeks array tidak valid Kesalahan NULL
mapExpr[kunci] Mengembalikan nilai mapExpr untuk kunci. Kunci peta tidak valid Kesalahan NULL
pembagi div dividen Menampilkan bagian integral dari pembagian divisor oleh dividen. 1 div 0 Kesalahan NULL

Functions

Perilaku beberapa fungsi bawaan dapat berbeda di bawah mode ANSI vs mode non-ANSI dalam kondisi yang ditentukan di bawah ini.

Pengoperasi Deskripsi Kondisi ANSI_MODE = benar ANSI_MODE = salah
abs (expr) Menghasilkan nilai absolut dari nilai numerik pada expr. abs(-128y) Kesalahan -128y (Luapan)
element_at(mapExpr, kunci) Mengembalikan nilai mapExpr untuk kunci. Kunci peta tidak valid Kesalahan NULL
element_at(arrayExpr, indeks) Mengembalikan elemen arrayExpr pada indeks. Indeks array tidak valid Kesalahan NULL
elt(index, expr1 [, ...] ) Mengembalikan ekspresi ke-n. Indeks tidak valid Kesalahan NULL
make_date (y, m, d) Membuat nilai tanggal dari bidang tahun, bulan, dan hari. Tanggal hasil tidak valid Kesalahan NULL
make_timestamp(y,m,d,h,mi,s[,tz]) Menghasilkan cap waktu dari kolom data. Stempel waktu hasil tidak valid Kesalahan NULL
make_interval(y,m,w,d,h,mi,s) Membuat interval dari bidang. Interval hasil tidak valid Kesalahan NULL
mod(dividen, pembagi) Mengembalikan sisanya setelah dividen/divisor. mod(1, 0) Kesalahan NULL
next_day(expr,dayOfWeek) Menghasilkan tanggal pertama setelah expr dan diberi nama sebagai dayOfWeek. Hari dalam seminggu tidak valid Kesalahan NULL
parse_url(url, partToExtract[, kunci]) Mengekstrak sebagian dari URL. URL tidak valid Kesalahan NULL
pmod (dividen, pembagi) Mengembalikan sisa positif setelah pembilang/penyebut. pmod(1, 0) Kesalahan NULL
Ukuran (Expr) Mengembalikan nilai kardinalitas dari expr. size(NULL) NULL -1
to_date(expr[,fmt]) Mengembalikan cast expr ke tanggal menggunakan pemformatan opsional. String expr atau format tidak valid Kesalahan NULL
to_timestamp(expr[,fmt]) Mengembalikan expr yang dikonversi ke stempel waktu dengan menggunakan format opsional. String expr atau format tidak valid Kesalahan NULL
to_unix_timestamp(expr[,fmt]) Mengembalikan stempel waktu di expr sebagai stempel waktu UNIX. String expr atau format tidak valid Kesalahan NULL
unix_timestamp([expr[, fmt]]) Mengembalikan stempel waktu UNIX dari waktu saat ini atau yang ditentukan. String expr atau format tidak valid Kesalahan NULL

** Aturan pemeranan

Aturan dan perilaku mengenai CAST lebih ketat dalam mode ANSI. Ini dapat dibagi ke dalam tiga kategori berikut:

Aturan konversi waktu kompilasi

Jenis sumber Jenis target Contoh ANSI_MODE = benar ANSI_MODE = salah
Boolean Penanda Waktu cast(TRUE AS TIMESTAMP) Kesalahan 1970-01-01 00:00:00.000001 UTC
Tanggal Boolean cast(DATE'2001-08-09' AS BOOLEAN) Kesalahan NULL
Penanda Waktu Boolean cast(TIMESTAMP'1970-01-01 00:00:00Z' AS BOOLEAN) Kesalahan FALSE
Numerik integral Biner cast(15 AS BINARY) Kesalahan representasi biner

Kesalahan runtime

Jenis sumber Jenis target Kondisi Contoh ANSI_MODE = benar ANSI_MODE = salah
string Bukan-string Input tidak valid cast('a' AS INTEGER) Kesalahan NULL
Larik, Struktur, Peta Larik, Struktur, Peta Input tidak valid cast(ARRAY('1','2','3') AS ARRAY<DATE>) Kesalahan NULL
Numerik Numerik Luapan cast(12345 AS BYTE) Kesalahan NULL
Numerik Numerik integral Pemangkasan cast(5.1 AS INTEGER) Kesalahan 5

Catatan

Untuk masing-masing casts ini, Anda dapat menggunakan try_cast bukan cast untuk mengembalikan NULL, bukan kesalahan.

Aturan pemaksaan tipe implisit

Di bawah ANSI_MODE = TRUE, Databricks SQL menggunakan aturan transmisi jenis data SQL yang jelas untuk:

Sebaliknya ANSI_MODE = FALSE tidak konsisten dan lebih longgar. Contohnya:

  • Saat menggunakan jenis STRING dengan operator aritmatika apa pun, string secara implisit ditransmisikan ke DOUBLE.
  • Saat membandingkan STRING dengan jenis numerik apa pun, string secara implisit ditransmisikan ke jenis yang dibandingkan dengannya.
  • Saat melakukan UNION, COALESCE, atau operasi lain di mana jenis yang paling umum harus ditemukan, semua jenis dialihkan ke STRING jika ada jenis STRING yang hadir.

Databricks merekomendasikan penggunaan fungsi cast atau try_cast eksplisit, bukan mengandalkan ANSI_MODE = FALSE.

Contoh

> 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
  • Last updated on