次の方法で共有

ANSI_MODE

  • [アーティクル]

適用対象: 「はい」のチェック マーク 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