ANSI_MODE

[アーティクル]
03/07/2024

適用対象: check marked yes Databricks SQL

ANSI_MODE 構成パラメーターは、組み込み関数とキャスト演算の主要な動作を制御します。

この記事では、Databricks SQL の ANSI モードについて説明します。 Databricks Runtime での ANSI 準拠については、「Databricks Runtime での ANSI 準拠」を参照してください。

設定

TRUE

ほとんどのデータベースやデータウェアハウスと同様に、特定の算術演算と型変換の処理方法について SQL 標準に従います。この標準に従うことで、データの品質、整合性、移植性が向上します。
FALSE

Databricks SQL では、Hive と互換性のある動作が使用されます。

このパラメーターは、SET ステートメントを使用してセッションレベルで、また、SQL 構成パラメーターかSQL ウェアハウス API を使用してグローバルレベルで設定できます。

システムの既定

Databricks SQL 2022.35 以降に追加されたアカウントの場合、システムの既定値は TRUE です。

詳しい説明

Databricks SQL リファレンスドキュメントでは、SQL 標準の動作が説明されています。

次のセクションでは、ANSI_MODE TRUE (ANSI モード) と FALSE (ANSI 以外のモード) の違いについて説明します。

オペレーター

ANSI 以外のモードでは、数値型に対して実行される算術演算でオーバーフロー値または NULL が返される場合がありますが、ANSI モードでは、このような演算によってエラーが返されます。

演算子	Description	例	ANSI_MODE = true	ANSI_MODE = false
dividend / divisor	divisor で除算された dividend を返します。	`1/0`	Error	`NULL`
- expr	expr の否定値を返します。	`-(-128y)`	Error	`-128y` (オーバーフロー)
expr1 - expr2	expr1 からの expr2 の減算を返します。	`-128y - 1y`	Error	`127y` (オーバーフロー)
expr1 + expr2	expr1 と expr2 の合計を返します。	`127y + 1y`	Error	`-128y` (オーバーフロー)
dividend % divisor	dividend / divisor 後の剰余を返します。	`1 % 0`	Error	`NULL`
multiplier * multiplicand	multiplicand によって乗算された multiplier を返します。	`100y * 100y`	Error	`16y` (オーバーフロー)
arrayExpr[index]	index にある arrayExpr の要素を返します。	無効な配列インデックス	Error	`NULL`
mapExpr[key]	key の mapExpr の値を返します。	無効なマップキー	Error	`NULL`
divisor div dividend	dividend による divisor の除算の整数部分を返します。	`1 div 0`	Error	`NULL`

関数

一部の組み込み関数の動作は、次に示す条件で、ANSI モードと ANSI 以外のモードで異なる場合があります。

演算子	説明	条件	ANSI_MODE = true	ANSI_MODE = false
abs(expr)	expr 内の数値の絶対値を返します。	`abs(-128y)`	Error	`-128y` (オーバーフロー)
element_at(mapExpr, key)	key の mapExpr の値を返します。	無効なマップキー	Error	`NULL`
element_at(arrayExpr, index)	index にある arrayExpr の要素を返します。	無効な配列インデックス	Error	`NULL`
elt(index, expr1 [, …] )	N 番目の式を返します。	無効なインデックス	Error	`NULL`
make_date(y,m,d)	年、月、日の各フィールドから日付を作成します。	無効な結果の日付	Error	`NULL`
make_timestamp(y,m,d,h,mi,s[,tz])	フィールドからタイムスタンプを作成します。	無効な結果のタイムスタンプ	Error	`NULL`
make_interval(y,m,w,d,h,mi,s)	フィールドから間隔を作成します。	無効な結果の間隔	Error	`NULL`
mod(dividend, divisor)	dividend / divisor 後の剰余を返します。	`mod(1, 0)`	Error	`NULL`
next_day(expr,dayOfWeek)	expr より後の dayOfWeek 名が付けられた最初の日付を返します。	無効な曜日	Error	`NULL`
parse_url(url, partToExtract[, key])	url から一部を抽出します。	無効な URL	Error	`NULL`
pmod(dividend, divisor)	dividend / divisor 後の正の剰余を返します。	`pmod(1, 0)`	Error	`NULL`
size(expr)	expr のカーディナリティを返します。	`size(NULL)`	`NULL`	`-1`
to_date(expr[,fmt])	任意の書式設定を使用した日付への expr キャストを返します。	無効な expr または書式指定文字列	Error	`NULL`
to_timestamp(expr[,fmt])	任意の書式設定を使用したタイムスタンプへの expr キャストを返します。	無効な expr または書式指定文字列	Error	`NULL`
to_unix_timestamp(expr[,fmt])	expr 内のタイムスタンプを UNIX タイムスタンプとして返します。	無効な expr または書式指定文字列	Error	`NULL`
unix_timestamp([expr[, fmt]])	現在または指定された時刻の UNIX タイムスタンプを返します。	無効な expr または書式指定文字列	Error	`NULL`

キャストの規則

CAST に関する規則と動作は、ANSI モードではより厳密です。これらは次の 3 つのカテゴリに分けることができます。

コンパイル時の変換規則
実行時エラー
暗黙的な強制型変換規則

コンパイル時の変換規則

変換元の型	変換後の型	例	ANSI_MODE = true	ANSI_MODE = false
Boolean	Timestamp	`cast(TRUE AS TIMESTAMP)`	Error	`1970-01-01 00:00:00.000001 UTC`
Date	Boolean	`cast(DATE'2001-08-09' AS BOOLEAN)`	Error	`NULL`
Timestamp	Boolean	`cast(TIMESTAMP'1970-01-01 00:00:00Z' AS BOOLEAN)`	Error	`FALSE`
整数	Binary	`cast(15 AS BINARY)`	Error	バイナリ表現

実行時エラー

変換元の型	変換後の型	条件	例	ANSI_MODE = true	ANSI_MODE = false
String	文字列以外	無効な入力	`cast('a' AS INTEGER)`	Error	`NULL`
配列、構造体、マップ	配列、構造体、マップ	無効な入力	`cast(ARRAY('1','2','3') AS ARRAY<DATE>)`	Error	`NULL`
数値	数値	オーバーフロー	`cast(12345 AS BYTE)`	Error	`NULL`
数値	整数	切り捨て	`cast(5.1 AS INTEGER)`	Error	`5`

注意

これらの各キャストで、cast ではなく try_cast を使用して、エラーではなく NULL を返すことができます。

暗黙的な強制型変換規則

ANSI_MODE = TRUE の場合、Databricks SQL では次に対して明確な SQL データの型キャストの規則が使用されます。

対照的に、ANSI_MODE = FALSE の場合は一貫性がなく、厳密ではありません。次に例を示します。

任意の算術演算子で STRING 型を使用する場合、文字列は暗黙的に DOUBLE にキャストされます。
STRING をいずれかの数値型と比較する場合、文字列は比較される型に暗黙的にキャストされます。
UNION、COALESCE、または最小共通型を見つける必要がある他の演算を実行する場合、STRING 型が存在する場合は、すべての型が STRING にキャストされます。

Databricks では、ANSI_MODE = FALSE に依存するのではなく、明示的な cast または try_cast 関数を使用することをお勧めします。

例

> 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