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 在全局级别设置此参数。

系统默认

系统默认值为 TRUE，适用于在 Databricks SQL 2022.35 及更高版本上添加的帐户。

详细说明

Databricks SQL 参考文档介绍了 SQL 标准行为。

以下部分介绍了 ANSI_MODE TRUE（ANSI 模式）和 FALSE（非 ANSI 模式）之间的区别。

运算符

在非 ANSI 模式下，对数值类型执行的算术运算可能会返回溢出值或 NULL，而在 ANSI 模式下，此类运算会返回错误。

函数

在下面指定的条件下，某些内置函数的行为在 ANSI 模式与非 ANSI 模式下可能有所不同。

强制转换规则

在 ANSI 模式下，关于 CAST 的规则和行为更加严格。 它们可以分为以下三类：

• 编译时转换规则
• 运行时错误
• 隐式类型强制转换规则

编译时转换规则

运行时错误

隐式类型强制转换规则

在 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