ANSI_MODE

Berlaku untuk: 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.

• SALAH

  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.

Default sistem

Nilai default sistem adalah TRUE untuk akun yang ditambahkan pada Databricks SQL 2022.35 ke atas.

Deskripsi terperinci

Dokumentasi referensi Databricks SQL menjelaskan perilaku standar SQL.

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

Operator

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

Operator	Deskripsi	Contoh	ANSI_MODE = true	ANSI_MODE = false
dividend / divisor	Mengembalikan dividen yang dibagi dengan divisor.	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)
dividend % divisor	Mengembalikan sisanya setelah dividen/divisor.	1 % 0	Kesalahan	NULL
multiplier * multiplicand	Mengembalikan pengali dikalikan dengan multiplicand.	100y * 100y	Kesalahan	16y (Luapan)
arrayExpr[index]	Mengembalikan elemen arrayExpr pada indeks.	Indeks array tidak valid	Kesalahan	NULL
mapExpr[key]	Mengembalikan nilai mapExpr untuk kunci.	Kunci peta tidak valid	Kesalahan	NULL
divisor div dividend	Menampilkan bagian integral dari pembagian divisor oleh dividen.	1 div 0	Kesalahan	NULL

Fungsi

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

Operator	Deskripsi	Kondisi	ANSI_MODE = true	ANSI_MODE = false
abs(expr)	Menghasilkan nilai absolut dari nilai numerik di expr.	abs(-128y)	Kesalahan	-128y (Luapan)
element_at(mapExpr, key)	Mengembalikan nilai mapExpr untuk kunci.	Kunci peta tidak valid	Kesalahan	NULL
element_at(arrayExpr, index)	Mengembalikan elemen arrayExpr pada indeks.	Indeks array tidak valid	Kesalahan	NULL
elt(index, expr1 [, …] )	Mengembalikan ekspresi nth.	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])	Membuat stempel waktu dari bidang.	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(dividend, divisor)	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[, key])	Mengekstrak sebagian dari url.	URL tidak valid	Kesalahan	NULL
pmod(dividend, divisor)	Mengembalikan sisanya setelah dividen/divisor.	pmod(1, 0)	Kesalahan	NULL
size(expr)	Mengembalikan kardinalitas 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 cast expr ke stempel waktu menggunakan pemformatan 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 transmisi

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

• Aturan konversi waktu kompilasi
• Kesalahan runtime
• Aturan konversi eksplisit jenis implisit

Aturan konversi waktu kompilasi

Jenis sumber	Jenis target	Contoh	ANSI_MODE = true	ANSI_MODE = false
Boolean	Tanda 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
Tanda 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 = true	ANSI_MODE = false
String	Non-string	Input tidak valid	cast('a' AS INTEGER)	Kesalahan	NULL
Array, Struct, Map	Array, Struct, Map	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	Pemotongan	cast(5.1 AS INTEGER)	Kesalahan	5

Catatan

Untuk masing-masing transmisi ini, Anda dapat menggunakan try_cast, bukan ditransmisikan untuk mengembalikan NULL, bukan kesalahan.

Aturan konversi eksplisit jenis implisit

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

• promosi jenis
• downcasting
• crosscasting

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 operasi UNION, COALESCE, atau operasi lainnya di mana jenis yang paling tidak umum harus ditemukan, semua jenis ditransmisikan ke STRING jika ada jenis STRING.

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

Terkait

• aturan jenis data SQL
• MENGATUR ULANG
• Pernyataan SET
• Mengonfigurasi parameter SQL
• SQL Warehouse API