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，而配置 spark.sql.ansi.enabled 默认情况下是禁用的。

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

算术运算

在 Spark SQL 中，默认情况下不会检查对数值类型（decimal 除外）执行的算术运算是否溢出。 这意味着，如果某个操作导致溢出，则结果与 Java/Scala 程序中的相应操作相同（例如，如果 2 个整数的和高于可表示的最大值，则结果为负数）。 另一方面，Spark SQL 为 decimal 溢出返回 null。 当 spark.sql.ansi.enabled 设置为 true 并且数值和区间算术运算中发生溢出时，它会在运行时抛出算术异常。

-- `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(Numeric AS Numeric): 如果该值超出目标数据类型的范围，则引发溢出异常。
• CAST(String AS (Numeric/Date/Timestamp/Timestamp_NTZ/Interval/Boolean)): 如果该值无法解析为目标数据类型，则引发运行时异常。
• CAST(Timestamp AS Numeric): 如果自 epoch 以来的秒数超出目标数据类型的范围，则引发溢出异常。
• CAST(Numeric AS Timestamp): 如果数值乘以 1000000（每秒微秒数）超出 Long 类型的范围，则引发溢出异常。
• CAST(Array AS Array): 如果在元素转换中存在任何问题，则引发异常。
• CAST(Map AS Map): 如果在键和值的转换中存在任何问题，则引发异常。
• CAST(Struct AS Struct): 如果在结构字段的转换中存在任何问题，则引发异常。
• CAST(Numeric AS String): 在将 decimal 值转换为字符串时，始终使用纯字符串表示形式，而不是在需要指数时使用科学计数法
• CAST(Interval AS Numeric): 如果日期时间间隔的微秒数或年月间隔的月份数超出目标数据类型的范围，则引发溢出异常。
• CAST(Numeric AS Interval): 如果数值乘以目标间隔的结束单位超出年月间隔的 Int 类型范围或日期时间间隔的 Long 类型范围，则引发溢出异常。

-- Examples of explicit casting

-- `spark.sql.ansi.enabled=true`
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. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.
== 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. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.

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` (This is a default behaviour)
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 不支持 interval 类型表列。

** 对于 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 表达式等表达式推导结果类型。
• 为数组和映射构造函数推导元素、键或值类型。如果最少通用类型解析为 FLOAT，则应用特殊规则。 对于 float 类型的值，如果任何类型是 INT、BIGINT 或 DECIMAL，则最少通用类型会被提升到 DOUBLE，以避免潜在的数字丢失。

-- 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 的函数调用

• 通常，它遵循 Store assignment 规则，即将输入值存储为 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。
• 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: 与除法运算符 / 相同，不同之处在于，它返回 NULL 结果，而不是在除以 0 时抛出异常。
• try_sum: 与函数 sum 相同，不同之处在于，它返回 NULL 结果，而不是在整数/十进制/间隔值溢出时抛出异常。
• try_avg: 与函数 avg 相同，不同之处在于，它返回 NULL 结果，而不是在十进制/间隔值溢出时抛出异常。
• try_element_at: 与函数 element_at 相同，不同之处在于，它返回 NULL 结果，而不是在数组索引超出范围时抛出异常。
• try_to_timestamp: 与函数 to_timestamp 相同，不同之处在于，它返回 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.enabled 和 spark.sql.ansi.enforceReservedKeywords 均为 false。

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