适用于: Databricks SQL
ANSI_MODE
配置参数控制内置函数和强制转换操作的关键行为。
本文介绍 Databricks SQL 中的 ANSI 模式。 有关 Databricks Runtime 中的 ANSI 合规性,请参阅 Databricks Runtime 中的 ANSI 合规性。
真
在处理某些算术运算和类型转换时遵循 SQL 标准,与大多数数据库和数据仓库类似。 遵循此标准可提高数据质量、完整性和可移植性。
假
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 (ANSI 模式设置为 false) |
---|---|---|---|---|
被除数/除数 | 返回被除数除以除数的结果。 | 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 (ANSI 模式设置为 false) |
---|---|---|---|---|
abs(expr) | 返回 expr 中数值的绝对值。 | abs(-128y) |
错误 | -128y (溢出) |
element_at(mapExpr, key) | 返回键的 mapExpr 值。 | 映射键无效 | 错误 | NULL |
element_at(arrayExpr,索引) | 返回 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 (ANSI 模式设置为 false) |
---|---|---|---|---|
布尔 | 时间戳 | cast(TRUE AS TIMESTAMP) |
错误 | 1970-01-01 00:00:00.000001 UTC |
日期 | 布尔 | cast(DATE'2001-08-09' AS BOOLEAN) |
错误 | NULL |
时间戳 | 布尔 | cast(TIMESTAMP'1970-01-01 00:00:00Z' AS BOOLEAN) |
错误 | FALSE |
整数数值 | 二进制 | cast(15 AS BINARY) |
错误 | 二进制表示形式 |
源类型 | 目标类型 | 条件 | 示例 | ANSI_MODE = true | ANSI_MODE = false (ANSI 模式设置为 false) |
---|---|---|---|---|---|
字符串 | 非字符串 | 无效输入 | 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);
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