ANSI_MODE

適用対象: Databricks SQL

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

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

設定

• 真

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

• 偽

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

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

システムの既定

2022 年 10 月 19 日以降に作成されたアカウントの場合、システムのデフォルトは TRUE です。

詳しい説明

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

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

オペレーター

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

関数

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

キャストの規則

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

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

コンパイル時の変換規則

実行時エラー

暗黙的な強制型変換規則

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

• 型の上位変換
• ダウンキャスト
• クロスキャスト

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

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

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

例

> 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);
  1.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

関連項目

• SQL データ型のルール
• RESET
• SET ステートメント
• SQL パラメーターを構成する
• SQL ウェアハウス API