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_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|
| dividend / divisor | 返回被除数除以除数的结果。 | 1/0 |
错误 | NULL |
| - expr | 返回 expr 的求反值。 | -(-128y) |
错误 | -128y（溢出） |
| expr1 - expr2 | 返回从 expr1 减去 expr2 的结果。 | -128y - 1y |
错误 | 127y（溢出） |
| expr1 + expr2 | 返回 expr1 和 expr2 的总和。 | 127y + 1y |
错误 | -128y（溢出） |
| dividend % divisor | 返回被除数除以除数后的余数。 | 1 % 0 |
错误 | NULL |
| multiplier * multiplicand | 返回乘数乘以被乘数的结果。 | 100y * 100y |
错误 | 16y（溢出） |
| arrayExpr[index] | 返回 arrayExpr 在索引处的元素。 | 数组索引无效 | 错误 | NULL |
| mapExpr[key] | 返回键的 mapExpr 值。 | 映射键无效 | 错误 | NULL |
| divisor div dividend | 返回被除数除以除数所得结果的整数部分。 | 1 div 0 |
错误 | NULL |
函数
在下面指定的条件下，某些内置函数的行为在 ANSI 模式与非 ANSI 模式下可能有所不同。
| 操作员 | 说明 | 条件 | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|
| abs(expr) | 返回 expr 中数值的绝对值。 | abs(-128y) |
错误 | -128y（溢出） |
| element_at(mapExpr, key) | 返回键的 mapExpr 值。 | 映射键无效 | 错误 | NULL |
| element_at(arrayExpr, index) | 返回 arrayExpr 在索引处的元素。 | 数组索引无效 | 错误 | NULL |
| elt(index, expr1 [, …] ) | 返回第 n 个表达式。 | 索引无效 | 错误 | NULL |
| make_date(y,m,d) | 通过年、月和日字段创建日期。 | 结果日期无效 | 错误 | NULL |
| make_timestamp(y,m,d,h,mi,s[,tz]) | 通过字段创建时间戳。 | 结果时间戳无效 | 错误 | NULL |
| make_interval(y,m,w,d,h,mi,s) | 通过字段创建间隔。 | 结果间隔无效 | 错误 | NULL |
| mod(dividend, divisor) | 返回被除数除以除数后的余数。 | mod(1, 0) |
错误 | NULL |
| next_day(expr,dayOfWeek) | 返回晚于 expr 并已按 dayOfWeek 中命名的第一个日期。 | 星期几无效 | 错误 | NULL |
| parse_url(url, partToExtract[, key]) | 从 url 中提取一部分。 | 无效的 URL | 错误 | NULL |
| pmod(dividend, divisor) | 返回被除数除以除数后的正余数。 | pmod(1, 0) |
错误 | NULL |
| size(expr) | 返回 expr 的基数。 | size(NULL) |
NULL |
-1 |
| to_date(expr[,fmt]) | 返回使用可选格式设置将 expr 转换为日期后的值。 | expr 或格式字符串无效 | 错误 | NULL |
| to_timestamp(expr[,fmt]) | 返回使用可选格式设置强制转换为某个时间戳的 expr。 | expr 或格式字符串无效 | 错误 | NULL |
| to_unix_timestamp(expr[,fmt]) | 将 expr 中的时间戳返回为 UNIX 时间戳。 | expr 或格式字符串无效 | 错误 | NULL |
| unix_timestamp([expr[, fmt]]) | 返回当前时间或指定时间的 UNIX 时间戳。 | expr 或格式字符串无效 | 错误 | NULL |
强制转换规则
在 ANSI 模式下，关于 CAST 的规则和行为更加严格。 它们可以分为以下三类：
编译时转换规则
| 源类型 | 目标类型 | 示例 | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|
| 布尔 | Timestamp | cast(TRUE AS TIMESTAMP) |
错误 | 1970-01-01 00:00:00.000001 UTC |
| 日期 | 布尔 | cast(DATE'2001-08-09' AS BOOLEAN) |
错误 | NULL |
| Timestamp | 布尔 | cast(TIMESTAMP'1970-01-01 00:00:00Z' AS BOOLEAN) |
错误 | FALSE |
| 整数数值 | 二进制 | cast(15 AS BINARY) |
错误 | 二进制表示形式 |
运行时错误
| 源类型 | 目标类型 | 条件 | 示例 | ANSI_MODE = true | ANSI_MODE = false |
|---|---|---|---|---|---|
| String | 非字符串 | 无效输入 | cast('a' AS INTEGER) |
错误 | NULL |
| 数组、结构、映射 | 数组、结构、映射 | 无效输入 | cast(ARRAY('1','2','3') AS ARRAY<DATE>) |
错误 | NULL |
| 数字 | 数字 | 溢出 | cast(12345 AS BYTE) |
错误 | NULL |
| 数值 | 整数数值 | 截断 | cast(5.1 AS INTEGER) |
错误 | 5 |
隐式类型强制转换规则
在 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