ANSI 合规性

在 Spark SQL 中，有两个选项可用于实现对 SQL 标准的合规：spark.sql.ansi.enabled 和 spark.sql.storeAssignmentPolicy（详见下表）。

默认情况下，spark.sql.ansi.enabled 为 true，Spark SQL 使用 ANSI 合规方言而非 Hive 合规方言。例如，如果 SQL 算子/函数的输入无效，Spark 将抛出运行时异常，而不是返回 null 结果。某些 ANSI 方言特性可能并非直接源自 ANSI SQL 标准，但其行为与 ANSI SQL 的风格保持一致。

此外，Spark SQL 有一个独立的选项来控制向表中插入行时的隐式转换行为。这些转换行为在标准中被定义为存储赋值（store assignment）规则。

默认情况下，spark.sql.storeAssignmentPolicy 为 ANSI，Spark SQL 遵循 ANSI 存储赋值规则。

以下小节介绍了启用 ANSI 模式后在算术运算、类型转换和 SQL 解析方面的行为变化。对于 Spark SQL 中的类型转换，共有三种类型，本文将逐一介绍：强制转换（cast）、存储赋值（store assignment）和类型强制转换（type coercion）。

算术运算

在 Spark SQL 中，默认情况下，对于间隔类型和数值类型的溢出，Spark 会在运行时抛出算术异常。如果 spark.sql.ansi.enabled 为 false，则 decimal 类型将产生 null 值，其他数值类型的行为与 Java/Scala 程序中相应的运算相同（例如，如果两个整数之和高于可表示的最大值，则结果为负数），这正是 Spark 3 或更旧版本的行为。

-- `spark.sql.ansi.enabled=true`
SELECT 2147483647 + 1;
org.apache.spark.SparkArithmeticException: [ARITHMETIC_OVERFLOW] integer overflow. Use 'try_add' to tolerate overflow and return NULL instead. If necessary set spark.sql.ansi.enabled to "false" to bypass this error.
== SQL(line 1, position 8) ==
SELECT 2147483647 + 1
       ^^^^^^^^^^^^^^

SELECT abs(-2147483648);
org.apache.spark.SparkArithmeticException: [ARITHMETIC_OVERFLOW] integer overflow. If necessary set spark.sql.ansi.enabled to "false" to bypass this error.

-- `spark.sql.ansi.enabled=false`
SELECT 2147483647 + 1;
+----------------+
|(2147483647 + 1)|
+----------------+
|     -2147483648|
+----------------+

SELECT abs(-2147483648);
+----------------+
|abs(-2147483648)|
+----------------+
|     -2147483648|
+----------------+

强制转换 (Cast)

当 spark.sql.ansi.enabled 设置为 true 时，使用 CAST 语法进行的显式转换会对标准中定义的非法转换模式抛出运行时异常，例如从字符串转换为整数。

此外，ANSI SQL 模式不允许以下在关闭 ANSI 模式时允许的类型转换：

• 数值 <=> 二进制 (Numeric <=> Binary)
• 日期 <=> 布尔 (Date <=> Boolean)
• 时间戳 <=> 布尔 (Timestamp <=> Boolean)
• 日期 => 数值 (Date => Numeric)

下表给出了 CAST 表达式中源数据类型和目标数据类型的有效组合。“Y”表示该组合在语法上有效且无限制，“N”表示该组合无效。

在上表中，所有使用新语法的 CAST 均标记为红色 Y

• CAST(数值 AS 数值)：如果值超出目标数据类型的范围，抛出溢出异常。
• CAST(字符串 AS (数值/日期/时间戳/Timestamp_NTZ/间隔/布尔))：如果值无法解析为目标数据类型，抛出运行时异常。
• CAST(时间戳 AS 数值)：如果自纪元以来的秒数超出目标数据类型的范围，抛出溢出异常。
• CAST(数值 AS 时间戳)：如果数值乘以 1000000（每秒微秒数）超出 Long 类型范围，抛出溢出异常。
• CAST(数组 AS 数组)：如果元素转换过程中出现任何错误，抛出异常。
• CAST(映射 AS 映射)：如果键或值的转换过程中出现任何错误，抛出异常。
• CAST(结构体 AS 结构体)：如果结构体字段的转换过程中出现任何错误，抛出异常。
• CAST(数值 AS 字符串)：在将十进制值转换为字符串时，始终使用普通的字符串表示形式，而不是在需要指数时使用科学计数法。
• CAST(间隔 AS 数值)：如果日-时间隔的微秒数或年-月间隔的月数超出目标数据类型的范围，抛出溢出异常。
• CAST(数值 AS 间隔)：如果数值乘以目标间隔的结束单位超出 Int 类型（年-月间隔）或 Long 类型（日-时间隔）范围，抛出溢出异常。

-- Examples of explicit casting

-- `spark.sql.ansi.enabled=true` (This is a default behaviour)
SELECT CAST('a' AS INT);
org.apache.spark.SparkNumberFormatException: [CAST_INVALID_INPUT] The value 'a' of the type "STRING" cannot be cast to "INT" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead.
== SQL(line 1, position 8) ==
SELECT CAST('a' AS INT)
       ^^^^^^^^^^^^^^^^

SELECT CAST(2147483648L AS INT);
org.apache.spark.SparkArithmeticException: [CAST_OVERFLOW] The value 2147483648L of the type "BIGINT" cannot be cast to "INT" due to an overflow. Use `try_cast` to tolerate overflow and return NULL instead.

SELECT CAST(DATE'2020-01-01' AS INT);
org.apache.spark.sql.AnalysisException: cannot resolve 'CAST(DATE '2020-01-01' AS INT)' due to data type mismatch: cannot cast date to int.
To convert values from date to int, you can use function UNIX_DATE instead.

-- `spark.sql.ansi.enabled=false`
SELECT CAST('a' AS INT);
+--------------+
|CAST(a AS INT)|
+--------------+
|          null|
+--------------+

SELECT CAST(2147483648L AS INT);
+-----------------------+
|CAST(2147483648 AS INT)|
+-----------------------+
|            -2147483648|
+-----------------------+

SELECT CAST(DATE'2020-01-01' AS INT)
+------------------------------+
|CAST(DATE '2020-01-01' AS INT)|
+------------------------------+
|                          null|
+------------------------------+

-- Examples of store assignment rules
CREATE TABLE t (v INT);

-- `spark.sql.storeAssignmentPolicy=ANSI`
INSERT INTO t VALUES ('1');
org.apache.spark.sql.AnalysisException: [INCOMPATIBLE_DATA_FOR_TABLE.CANNOT_SAFELY_CAST] Cannot write incompatible data for table `spark_catalog`.`default`.`t`: Cannot safely cast `v`: "STRING" to "INT".

-- `spark.sql.storeAssignmentPolicy=LEGACY` (This is a legacy behaviour until Spark 2.x)
INSERT INTO t VALUES ('1');
SELECT * FROM t;
+---+
|  v|
+---+
|  1|
+---+

强制转换中的舍入

当将带有小数的十进制数转换为以 SECOND 为结束单位的间隔类型（如 INTERVAL HOUR TO SECOND）时，除非两个邻近值等距，否则 Spark 会将小数部分向“最近邻”舍入，如果等距则向上舍入。

存储赋值

如开头所述，当 spark.sql.storeAssignmentPolicy 设置为 ANSI（默认值）时，Spark SQL 在表插入时遵循 ANSI 存储赋值规则。表插入中源数据类型和目标数据类型的有效组合如下表所示。

* Spark 不支持间隔类型表列。

** 对于 Array/Map/Struct 类型，数据类型检查规则递归应用于其组件元素。

在表插入过程中，如果出现数值溢出，Spark 将抛出异常。

CREATE TABLE test(i INT);

INSERT INTO test VALUES (2147483648L);
org.apache.spark.SparkArithmeticException: [CAST_OVERFLOW_IN_TABLE_INSERT] Fail to insert a value of "BIGINT" type into the "INT" type column `i` due to an overflow. Use `try_cast` on the input value to tolerate overflow and return NULL instead.

类型强制转换 (Type Coercion)

类型提升与优先级

当 spark.sql.ansi.enabled 设置为 true 时，Spark SQL 使用若干规则来管理数据类型冲突的解决方式。这种冲突解决的核心是“类型优先级列表”，它定义了给定数据类型的值是否可以隐式提升为另一种数据类型。

* 对于最小公共类型解析，跳过 float 以避免精度损失。

** String 可以提升为多种数据类型。请注意，Byte/Short/Int/Decimal/Float 不在此优先级列表中。Byte/Short/Int 与 String 之间的最小公共类型是 Long，而 Decimal/Float 之间是 Double。

*** 对于复杂类型，优先级规则递归应用于其组件元素。

无类型 NULL 适用特殊规则。NULL 可以提升为任何其他类型。

这是优先级列表的定向树状图：

最小公共类型解析

一组类型中的最小公共类型是优先级列表中所有类型元素可到达的最窄类型。

最小公共类型解析用于：

• 为需要多个参数共享同一参数类型的函数（如 coalesce, least, greatest）推导参数类型。
• 为运算符（如算术运算或比较）推导操作数类型。
• 为表达式（如 case 表达式）推导结果类型。
• 推导数组和映射构造函数的元素、键或值类型。如果最小公共类型解析为 FLOAT，则适用特殊规则。对于浮点类型值，如果任何类型为 INT、BIGINT 或 DECIMAL，最小公共类型将推至 DOUBLE 以避免潜在的数字丢失。

Decimal 类型在这里较为复杂，因为它不是一个简单类型，而是带有参数：精度（precision）和标度（scale）。decimal(precision, scale) 表示该值在整数部分最多有 precision - scale 位，在小数部分有 scale 位。十进制类型之间的最小公共类型应在整数和小数部分都有足够的位数来表示所有值。更准确地说，decimal(p1, s1) 和 decimal(p2, s2) 之间的最小公共类型的标度为 max(s1, s2)，精度为 max(s1, s2) + max(p1 - s1, p2 - s2)。但是，Spark 中的十进制类型有最大精度限制：38。如果最终的十进制类型需要更多精度，必须进行截断。由于整数部分的数字权重更高，Spark 会优先截断小数部分的数字。例如，decimal(48, 20) 将被缩减为 decimal(38, 10)。

注意，算术运算有计算十进制输入最小公共类型的特殊规则：

算术运算的截断规则也不同：它们在小数部分保留至少 6 位，这意味着我们将标度缩小到 6 时可能会发生溢出。

-- The coalesce function accepts any set of argument types as long as they share a least common type. 
-- The result type is the least common type of the arguments.
> SET spark.sql.ansi.enabled=true;
> SELECT typeof(coalesce(1Y, 1L, NULL));
BIGINT
> SELECT typeof(coalesce(1, DATE'2020-01-01'));
Error: Incompatible types [INT, DATE]

> SELECT typeof(coalesce(ARRAY(1Y), ARRAY(1L)));
ARRAY<BIGINT>
> SELECT typeof(coalesce(1, 1F));
DOUBLE
> SELECT typeof(coalesce(1L, 1F));
DOUBLE
> SELECT (typeof(coalesce(1BD, 1F)));
DOUBLE

> SELECT typeof(coalesce(1, '2147483648'))
BIGINT
> SELECT typeof(coalesce(1.0, '2147483648'))
DOUBLE
> SELECT typeof(coalesce(DATE'2021-01-01', '2022-01-01'))
DATE

SQL 函数

函数调用

在 ANSI 模式下 (spark.sql.ansi.enabled=true)，Spark SQL 的函数调用：

• 通常遵循“存储赋值”规则，即将输入值存储为 SQL 函数声明的参数类型。
• 无类型 NULL 适用特殊规则。NULL 可以提升为任何其他类型。

> SET spark.sql.ansi.enabled=true;
-- implicitly cast Int to String type
> SELECT concat('total number: ', 1);
total number: 1
-- implicitly cast Timestamp to Date type
> select datediff(now(), current_date);
0

-- implicitly cast String to Double type
> SELECT ceil('0.1');
1
-- special rule: implicitly cast NULL to Date type
> SELECT year(null);
NULL

> CREATE TABLE t(s string);
-- Can't store String column as Numeric types.
> SELECT ceil(s) from t;
Error in query: cannot resolve 'CEIL(spark_catalog.default.t.s)' due to data type mismatch
-- Can't store String column as Date type.
> select year(s) from t;
Error in query: cannot resolve 'year(spark_catalog.default.t.s)' due to data type mismatch

行为不同的函数

某些 SQL 函数在 ANSI 模式 (spark.sql.ansi.enabled=true) 下的行为可能不同。

• size: 此函数对于 null 输入返回 null。
• element_at:
  • 如果使用无效索引，此函数抛出 ArrayIndexOutOfBoundsException。
• elt: 如果使用无效索引，此函数抛出 ArrayIndexOutOfBoundsException。
• parse_url: 如果输入字符串不是有效的 url，此函数抛出 IllegalArgumentException。
• to_date: 如果无法解析输入字符串或模式字符串无效，此函数应报错。
• to_timestamp: 如果无法解析输入字符串或模式字符串无效，此函数应报错。
• unix_timestamp: 如果无法解析输入字符串或模式字符串无效，此函数应报错。
• to_unix_timestamp: 如果无法解析输入字符串或模式字符串无效，此函数应报错。
• make_date: 如果结果日期无效，此函数应报错。
• make_timestamp: 如果结果时间戳无效，此函数应报错。
• make_interval: 如果结果间隔无效，此函数应报错。
• next_day: 如果输入不是有效的星期几，此函数抛出 IllegalArgumentException。

SQL 运算符

某些 SQL 运算符在 ANSI 模式 (spark.sql.ansi.enabled=true) 下的行为可能不同。

• array_col[index]: 如果使用无效索引，此运算符抛出 ArrayIndexOutOfBoundsException。

ANSI 模式下的有用函数

当 ANSI 模式开启时，无效操作会抛出异常。您可以使用以下 SQL 函数来抑制此类异常。

• try_cast: 与 CAST 相同，但在运行时错误时返回 NULL 而非抛出异常。
• try_add: 与加法运算符 + 相同，但在整数溢出时返回 NULL 而非抛出异常。
• try_subtract: 与减法运算符 - 相同，但在整数溢出时返回 NULL 而非抛出异常。
• try_multiply: 与乘法运算符 * 相同，但在整数溢出时返回 NULL 而非抛出异常。
• try_divide: 与除法运算符 / 相同，但在除以 0 时返回 NULL 而非抛出异常。
• try_mod: 与取模运算符 % 相同，但在除以 0 时返回 NULL 而非抛出异常。
• try_sum: 与 sum 函数相同，但在整数/十进制/间隔溢出时返回 NULL 而非抛出异常。
• try_avg: 与 avg 函数相同，但在十进制/间隔溢出时返回 NULL 而非抛出异常。
• try_element_at: 与 element_at 函数相同，但在数组索引越界时返回 NULL 而非抛出异常。
• try_to_timestamp: 与 to_timestamp 函数相同，但在字符串解析错误时返回 NULL 而非抛出异常。
• try_parse_url: 与 parse_url 函数相同，但在 url 解析错误时返回 NULL 而非抛出异常。
• try_make_timestamp: 与 make_timestamp 函数相同，但在出错时返回 NULL 而非抛出异常。
• try_make_timestamp_ltz: 与 make_timestamp_ltz 函数相同，但在出错时返回 NULL 而非抛出异常。
• try_make_timestamp_ntz: 与 make_timestamp_ntz 函数相同，但在出错时返回 NULL 而非抛出异常。
• try_make_interval: 与 make_interval 函数相同，但在间隔无效时返回 NULL 而非抛出异常。
• try_to_time: 与 to_time 函数相同，但在字符串解析错误时返回 NULL 而非抛出异常。
• try_to_date: 与 to_date 函数相同，但在字符串解析错误时返回 NULL 而非抛出异常。

SQL 关键字（可选，默认禁用）

当 spark.sql.ansi.enabled 和 spark.sql.ansi.enforceReservedKeywords 均为 true 时，Spark SQL 将使用 ANSI 模式解析器。

在 ANSI 模式解析器下，Spark SQL 有两种关键字：

• 非保留关键字：仅在特定上下文中具有特殊含义，在其他上下文中可用作标识符的关键字。例如，EXPLAIN SELECT ... 是一个命令，但在其他地方 EXPLAIN 可用作标识符。
• 保留关键字：被保留且不能用作表、视图、列、函数、别名等标识符的关键字。

使用默认解析器时，Spark SQL 有两种关键字：

• 非保留关键字：与启用 ANSI 模式时的定义相同。
• 严格非保留关键字：非保留关键字的严格版本，不能用作表别名。

默认情况下，spark.sql.ansi.enforceReservedKeywords 为 false。

以下是 Spark SQL 中所有关键字的列表。