Databricks Runtime 中的 ANSI 合规性

适用于： Databricks Runtime

本文介绍 Databricks Runtime 中的 ANSI 合规性。 有关 Databricks SQL 中的 ANSI 模式，请参阅 ANSI_MODE。

Spark SQL 提供两个有助于符合 ANSI 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 中的类型转换有三种，本文将一一介绍它们：强制转换、存储分配和类型强制转换。

算术运算

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

-- `spark.sql.ansi.enabled=true`
> SELECT 2147483647 + 1;
 error: integer overflow

-- `spark.sql.ansi.enabled=false`
> SELECT 2147483647 + 1;
  -2147483648

强制转换

根据标准中定义的非法强制转换模式，将 spark.sql.ansi.enabled 设置为 true 时，通过 CAST 语法进行显式强制转换（例如将字符串转换为整数）会引发运行时异常。

Spark ANSI 模式的 CAST 子句遵循 ISO/IEC 9075-2:2011 信息技术 — 数据库语言 - SQL — 第 2 部分：Foundation (SQL/Foundation) 中 6.13 节“强制转换规范”的语法规则，但它特别允许 ANSI 标准禁止的以下简单类型转换：

• 数字类型 <=> 布尔类型
• 字符串类型 <=> 二进制类型

下表提供了 CAST 表达式中源数据类型和目标数据类型的有效组合。 “Y”表示组合在语法上有效（没有限制），“N”表示组合无效。

-- Examples of explicit casting

-- `spark.sql.ansi.enabled=true`
> SELECT CAST('a' AS INT);
  ERROR: [CAST_INVALID_INPUT] The value 'a' of the type "STRING" cannot be cast to "INT" because it is malformed.

> SELECT CAST(2147483648L AS INT);
  ERROR: [CAST_OVERFLOW] The value 2147483648L of the type "BIGINT" cannot be cast to "INT" due to an overflow.

> SELECT CAST(DATE'2020-01-01' AS INT)
  ERROR: [DATATYPE_MISMATCH.CAST_WITH_FUNC_SUGGESTION] Cannot resolve "CAST(DATE '2020-01-01' AS INT)" due to data type mismatch: cannot cast "DATE" to "INT".

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

> SELECT CAST(2147483648L AS INT);
  -2147483648

> SELECT CAST(DATE'2020-01-01' AS INT);
  null

存储分配

此设置 spark.sql.storeAssignmentPolicy 默认设置为 ANSI. 使用此设置时，当源值的数据类型与目标列类型不匹配时，Spark SQL 会自动向 INSERT 语句添加 ANSI CAST 子句。 在此策略下插入表期间，Spark 会检查并拒绝无效强制转换，引发异常以确保数据质量。 这意味着，如果插入尝试由于类型不匹配而失败，则不会导致任何数据部分写入表中。

示例：

-- spark.sql.storeAssignmentPolicy=ANSI
> CREATE TABLE test(i INT);
> INSERT INTO test VALUES (2147483648L);
  ERROR: [CAST_OVERFLOW_IN_TABLE_INSERT] Fail to insert a value of "BIGINT" type into the "INT" type column `i` due to an overflow.

> INSERT INTO test VALUES ('a');
  ERROR: [CAST_INVALID_INPUT ERROR] The value 'a' of the type "STRING" cannot be cast to "INT" because it is malformed

这些示例显示 Spark SQL 会防止插入不兼容的数据，从而保持数据完整性。

当 spark.sql.storeAssignmentPolicy 设置为 LEGACY 时，Spark SQL 会回退到 Spark 2.x 之前普遍存在的行为。 在此模式下，它会应用旧的 CAST 操作，而不是使用 ANSI CAST。 在此策略下，在表插入期间无效强制转换会导致插入 NULL 值或错误值，而不是引发异常。 示例：

-- spark.sql.storeAssignmentPolicy=LEGACY
> CREATE TABLE test(i INT);
> INSERT INTO test VALUES (2147483648L);
> INSERT INTO test VALUES ('a');
> SELECT * FROM test;

-- Results
--  -2147483648 (incorrect value due to overflow)
--  null (cannot cast 'a' to INT)

类型强制

类型提升和优先级

将 spark.sql.ansi.enabled 设置为 true 时，Spark SQL 使用几个规则来管理解决数据类型之间冲突的方式。 此冲突解决方法的核心是类型优先级列表，该列表定义给定数据类型的值是否可以隐式提升为另一种数据类型。

• 对于最不常见的类型解析，会跳过 Float 以避免精度损失。

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

特殊规则适用于字符串类型和非类型化 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

-- The substring function expects arguments of type INT for the start and length parameters.
> SELECT substring('hello', 1Y, 2);
he

> SELECT substring('hello', '1', 2);
he

> SELECT substring('hello', 1L, 2);
Error: Argument 2 requires an INT type.

> SELECT substring('hello', str, 2) FROM VALUES(CAST('1' AS STRING)) AS T(str);
Error: Argument 2 requires an INT type.

SQL 函数

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

• size：在 ANSI 模式下，对于 NULL 输入，此函数将返回 NULL。
• %>
  • 如果使用无效索引，则此函数会引发 ArrayIndexOutOfBoundsException。
  • 如果映射中不存在键，则此函数会引发 NoSuchElementException。
• elt：如果使用无效索引，则此函数会引发 ArrayIndexOutOfBoundsException。
• make_date：如果结果日期无效，则此函数会失败并出现异常。
• make_timestamp：如果结果时间戳无效，则此函数会失败并出现异常。
• make_interval：如果结果间隔无效，则此函数会失败并出现异常。
• next_day：如果输入不是有效的星期几，则此函数会引发 IllegalArgumentException。
• parse_url：如果输入字符串不是有效的 URL，则此函数会引发 IllegalArgumentException。
• to_date：如果无法分析输入字符串或模式字符串无效，则此函数会失败并出现异常。
• to_timestamp：如果无法分析输入字符串或模式字符串无效，则此函数会失败并出现异常。
• to_unix_timestamp：如果无法分析输入字符串或模式字符串无效，则此函数会失败并出现异常。
• unix_timestamp：如果无法分析输入字符串或模式字符串无效，则此函数会失败并出现异常。

SQL 运算符

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

• array_col[index]：如果使用无效索引，则此运算符会引发 ArrayIndexOutOfBoundsException。
• map_col[key]：如果映射中不存在键，则此运算符会引发 NoSuchElementException。
• CAST(string_col AS TIMESTAMP)：如果无法分析输入字符串，则此运算符会失败并出现异常。
• CAST(string_col AS DATE)：如果无法分析输入字符串，则此运算符会失败并出现异常。

适用于 ANSI 模式的函数

ANSI 模式打开时，会针对无效操作引发异常。 可以使用以下 SQL 函数来阻止此类异常。

• try_cast：与 CAST 相同，只不过它返回 NULL 结果而不是在出现运行时错误时引发异常。
• try_add：与加法运算符 + 相同，只不过它返回 NULL 结果而不是在整型值溢出时引发异常。
• try_divide：与除法运算符 / 相同，只不过它返回 NULL 结果而不是在除以 0 时引发异常。

SQL 关键字

如果 spark.sql.ansi.enabled 为 true，Spark SQL 将使用 ANSI 模式分析器。 在此模式下，Spark SQL 有两种关键字：

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

禁用 ANSI 模式时，Spark SQL 有两种关键字：

• 非保留关键字：与 ANSI 模式处于启用状态下的定义相同。
• 严格非保留关键字：非保留关键字的 strict 版本，无法用作表别名。

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

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