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 模式中，這類作業會傳回錯誤。

Functions

某些內建函式的行為在 ANSI 模式與非 ANSI 模式下，在以下指定的條件下可能會有所不同。

轉型規則

關於 CAST 的規則和行為在 ANSI 模式中更嚴格。 它們可以分成下列三個類別：

• 編譯時間轉換規則
• 執行階段錯誤
• 隱含類型強制規則

編譯時間轉換規則

執行階段錯誤

隱含類型強制規則

在 下 ANSI_MODE = TRUE，Databricks SQL 會針對下列專案使用明確的 SQL 數據類型轉換規則 ：

• 型別提升
• 向下轉型
• 交叉傳播

相比之下，ANSI_MODE = FALSE 前後不一，且更加寬鬆。 例如：

• 搭配任何算術運算子使用 STRING 型別時，字串會隱含轉換成 DOUBLE。
• 比較 與 STRING 任何數值類型時，字串會隱含轉換成它所比較的類型。
• 執行UNION、COALESCE或其他作業時，如果有任何STRING類型存在，則必須找到最小公共型別，所有類型都會轉換成STRING。

Databricks 建議使用明確 轉換 或 try_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);
  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

相關項目

• SQL 資料類型規則
• RESET
• SET 聲明
• 設定 SQL 參數
• SQL 倉儲 API