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 还有一个独立的选项来控制在向表中插入行时的隐式类型转换行为。这些类型转换行为在标准中被定义为存储赋值规则。

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

以下小节将介绍启用 ANSI 模式时算术运算、类型转换和 SQL 解析中的行为变化。对于 Spark SQL 中的类型转换，有三种类型，本文将逐一介绍：cast、存储赋值和类型强制转换。

算术运算

在 Spark SQL 中，默认情况下，Spark 会在运行时对间隔类型和数值类型溢出抛出算术异常。如果 spark.sql.ansi.enabled 为 false，则十进制类型将生成 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 模式关闭时允许的类型转换

• 数值 <=> 二进制
• 日期 <=> 布尔
• 时间戳 <=> 布尔
• 日期 => 数值

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

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

• CAST(数值 AS 数值)：如果值超出目标数据类型的范围，则抛出溢出异常。
• CAST(字符串 AS (数值/日期/时间戳/Timestamp_NTZ/间隔/布尔))：如果值无法解析为目标数据类型，则抛出运行时异常。
• CAST(时间戳 AS 数值)：如果自纪元以来的秒数超出目标数据类型的范围，则抛出溢出异常。
• CAST(数值 AS 时间戳)：如果数值乘以 1000000（每秒微秒数）超出 Long 类型的范围，则抛出溢出异常。
• CAST(数组 AS 数组)：如果元素转换中存在任何问题，则抛出异常。
• CAST(Map AS Map)：如果键和值的转换中存在任何问题，则抛出异常。
• CAST(Struct AS Struct)：如果结构体字段的转换中存在任何问题，则抛出异常。
• 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|
+---+

Cast 中的舍入

在将带小数部分的 decimal 类型转换为以 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.

类型强制转换

类型提升和优先级

当 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 表达式）的结果类型。
• 推导数组和 Map 构造函数中的元素、键或值类型。如果最小公共类型解析为 FLOAT，则应用特殊规则。对于 float 类型值，如果任何类型是 INT、BIGINT 或 DECIMAL，则最小公共类型会被推送到 DOUBLE，以避免潜在的数字损失。

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

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

算术运算的截断规则也不同：它们在小数部分至少保留 6 位数字，这意味着我们只能将 scale 减少到 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

行为不同的函数

在 ANSI 模式下（spark.sql.ansi.enabled=true），一些 SQL 函数的行为可能不同。

• 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 运算符

在 ANSI 模式下（spark.sql.ansi.enabled=true），一些 SQL 运算符的行为可能不同。

• 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 结果而不是抛出异常。

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 中所有关键字的列表。