ANSI 合规性ANSI compliance

• 12/22/2020
• • m

Spark SQL 提供两个试验性选项以支持符合 ANSI SQL 标准：spark.sql.ansi.enabled 和 spark.sql.storeAssignmentPolicy。Spark SQL has two experimental options to support compliance with the ANSI SQL standard: spark.sql.ansi.enabled and spark.sql.storeAssignmentPolicy.

如果 spark.sql.ansi.enabled 设置为 true，Spark SQL 将遵循基本行为中的标准（例如，算术运算、类型转换、SQL 函数和 SQL 分析）。When spark.sql.ansi.enabled is set to true, Spark SQL follows the standard in basic behaviors (for example, arithmetic operations, type conversion, SQL functions and SQL parsing). 此外，在表中插入行时，Spark SQL 提供独立的选项来控制隐式强制转换行为。Moreover, Spark SQL has an independent option to control implicit casting behaviors when inserting rows in a table. 强制转换行为在标准为定义为存储分配规则。The casting behaviors are defined as store assignment rules in the standard.

将 spark.sql.storeAssignmentPolicy 设置为 ANSI 时，Spark SQL 将遵循 ANSI 存储分配规则。When spark.sql.storeAssignmentPolicy is set to ANSI, Spark SQL complies with the ANSI store assignment rules. 这是一个单独的配置，因为其默认值为 ANSI，而配置 spark.sql.ansi.enabled 默认处于禁用状态。This is a separate configuration because its default value is ANSI, while the configuration spark.sql.ansi.enabled is disabled by default.

下表对此行为进行了汇总：The following table summarizes the behavior:

以下各子部分显示了启用 ANSI 模式时算术运算、类型转换和 SQL 分析的行为变更。The following subsections present behavior changes in arithmetic operations, type conversions, and SQL parsing when ANSI mode is enabled.

算术运算Arithmetic operations

在 Spark SQL 中，默认情况下，不会检查对数值类型（十进制数除外）执行的算术运算是否溢出。In Spark SQL, arithmetic operations performed on numeric types (with the exception of decimal) are not checked for overflows by default. 这意味着，如果某个操作导致溢出，则结果与 Java 或 Scala 程序中相应的操作结果相同（例如，如果 2 个整数的和大于可表示的最大值，则结果为负数）。This means that in case an operation causes overflows, the result is the same with the corresponding operation in a Java or Scala program (For example, if the sum of 2 integers is higher than the maximum value representable, the result is a negative number). 另一方面，对于十进制数溢出，Spark SQL 会返回 NULL。On the other hand, Spark SQL returns null for decimal overflows. 将 spark.sql.ansi.enabled 设置为 true，并且数值和间隔算术运算中发生溢出时，它会在运行时引发算术异常。When spark.sql.ansi.enabled is set to true and an overflow occurs in numeric and interval arithmetic operations, it throws an arithmetic exception at runtime.

-- `spark.sql.ansi.enabled=true`
SELECT 2147483647 + 1;
java.lang.ArithmeticException: integer overflow

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

类型转换Type conversion

Spark SQL 具有三种类型的转换：显式强制转换、类型强制转换和存储分配转换。Spark SQL has three kinds of type conversions: explicit casting, type coercion, and store assignment casting. 将 spark.sql.ansi.enabled 设置为 true 时，通过 CAST 语法进行显式强制转换会在标准中定义的非法强制转换模式下引发运行时异常，例如将字符串转换为整数。When spark.sql.ansi.enabled is set to true, explicit casting by CAST syntax throws a runtime exception for illegal cast patterns defined in the standard, e.g. casts from a string to an integer. 另一方面，通过 spark.sql.storeAssignmentPolicy=ANSI 启用 ANSI 模式时，INSERT INTO 语法会引发分析异常。On the other hand, INSERT INTO syntax throws an analysis exception when the ANSI mode enabled via spark.sql.storeAssignmentPolicy=ANSI.

目前，ANSI 模式仅影响显式强制转换和分配强制转换。Currently, the ANSI mode affects explicit casting and assignment casting only. 在将来的版本中，类型强制转换的行为可能会随其他两种类型转换规则一起更改。In future releases, the behavior of type coercion might change along with the other two type conversion rules.

-- Examples of explicit casting

-- `spark.sql.ansi.enabled=true`
SELECT CAST('a' AS INT);
java.lang.NumberFormatException: invalid input syntax for type numeric: a

SELECT CAST(2147483648L AS INT);
java.lang.ArithmeticException: Casting 2147483648 to int causes overflow

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

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

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

-- `spark.sql.storeAssignmentPolicy=ANSI`
INSERT INTO t VALUES ('1');
org.apache.spark.sql.AnalysisException: Cannot write incompatible data to table '`default`.`t`':
- Cannot safely cast 'v': string to int;

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

SQL 函数SQL functions

在 ANSI 模式 (spark.sql.ansi.enabled=true) 下，某些 SQL 函数的行为可能会有所不同。The behavior of some SQL functions can be different under ANSI mode (spark.sql.ansi.enabled=true).

• size：在 ANSI 模式下，对于 NULL 输入，此函数将返回 NULL。size: This function returns null for null input under ANSI mode.

SQL 关键字SQL keywords

如果 spark.sql.ansi.enabled 为 true，Spark SQL 将使用 ANSI 模式分析器。When spark.sql.ansi.enabled is true, Spark SQL will use the ANSI mode parser. 在此模式下，Spark SQL 有两种关键字：In this mode, Spark SQL has two kinds of keywords:

• 保留关键字：保留的关键字，不能用作表、视图、列、函数、别名等的标识符。Reserved keywords: Keywords that are reserved and can’t be used as identifiers for table, view, column, function, alias, etc.
• 非保留关键字：仅在特定上下文中具有特殊含义的关键字，在其他上下文中可用作标识符。Non-reserved keywords: Keywords that have a special meaning only in particular contexts and can be used as identifiers in other contexts. 例如，EXPLAIN SELECT ... 是一个命令，但 EXPLAIN 在其他位置可用作标识符。For example, EXPLAIN SELECT ... is a command, but EXPLAIN can be used as identifiers in other places.

禁用 ANSI 模式时，Spark SQL 有两种关键字：When the ANSI mode is disabled, Spark SQL has two kinds of keywords:

• 非保留关键字：与 ANSI 模式处于启用状态下的定义相同。Non-reserved keywords: Same definition as the one when the ANSI mode enabled.
• 严格非保留关键字：非保留关键字的 strict 版本，无法用作表别名。Strict-non-reserved keywords: A strict version of non-reserved keywords, which cannot be used as table alias.

默认情况下，spark.sql.ansi.enabled 为 false。By default spark.sql.ansi.enabled is false.

下面是 Spark SQL 中所有关键字的列表。Below is a list of all the keywords in Spark SQL.