ANSI Compliance

Since Spark 3.0, Spark SQL introduces two experimental options to comply with the SQL standard: spark.sql.ansi.enabled and spark.sql.storeAssignmentPolicy (See a table below for details).

When spark.sql.ansi.enabled is set to true, Spark SQL uses an ANSI compliant dialect instead of being Hive compliant. For example, Spark will throw an exception at runtime instead of returning null results if the inputs to a SQL operator/function are invalid. Some ANSI dialect features may be not from the ANSI SQL standard directly, but their behaviors align with ANSI SQL’s style.

Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table. The casting behaviours are defined as store assignment rules in the standard.

When spark.sql.storeAssignmentPolicy is set to ANSI, Spark SQL complies with the ANSI store assignment rules. This is a separate configuration because its default value is ANSI, while the configuration spark.sql.ansi.enabled is disabled by default.

The following subsections present behaviour changes in arithmetic operations, type conversions, and SQL parsing when the ANSI mode enabled.

Arithmetic Operations

In Spark SQL, arithmetic operations performed on numeric types (with the exception of decimal) are not checked for overflows by default. This means that in case an operation causes overflows, the result is the same with the corresponding operation in a Java/Scala program (e.g., if the sum of 2 integers is higher than the maximum value representable, the result is a negative number). On the other hand, Spark SQL returns null for decimal overflows. 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 has three kinds of type conversions: explicit casting, type coercion, and store assignment casting. 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. On the other hand, INSERT INTO syntax throws an analysis exception when the ANSI mode enabled via spark.sql.storeAssignmentPolicy=ANSI.

The type conversion of Spark ANSI mode follows the syntax rules of section 6.13 “cast specification” in ISO/IEC 9075-2:2011 Information technology — Database languages - SQL — Part 2: Foundation (SQL/Foundation), except it specially allows the following straightforward type conversions which are disallowed as per the ANSI standard:

• NumericType <=> BooleanType
• StringType <=> BinaryType

The valid combinations of target data type and source data type in a CAST expression are given by the following table. “Y” indicates that the combination is syntactically valid without restriction and “N” indicates that the combination is not valid.

Currently, the ANSI mode affects explicit casting and assignment casting only. In future releases, the behaviour 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

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: Cannot write incompatible data to table '`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|
+---+

SQL Functions

The behavior of some SQL functions can be different under ANSI mode (spark.sql.ansi.enabled=true).

• size: This function returns null for null input.
• element_at:
  • This function throws ArrayIndexOutOfBoundsException if using invalid indices.
  • This function throws NoSuchElementException if key does not exist in map.
• elt: This function throws ArrayIndexOutOfBoundsException if using invalid indices.
• parse_url: This function throws IllegalArgumentException if an input string is not a valid url.
• to_date: This function should fail with an exception if the input string can’t be parsed, or the pattern string is invalid.
• to_timestamp: This function should fail with an exception if the input string can’t be parsed, or the pattern string is invalid.
• unix_timestamp: This function should fail with an exception if the input string can’t be parsed, or the pattern string is invalid.
• to_unix_timestamp: This function should fail with an exception if the input string can’t be parsed, or the pattern string is invalid.
• make_date: This function should fail with an exception if the result date is invalid.
• make_timestamp: This function should fail with an exception if the result timestamp is invalid.
• make_interval: This function should fail with an exception if the result interval is invalid.

SQL Operators

The behavior of some SQL operators can be different under ANSI mode (spark.sql.ansi.enabled=true).

• array_col[index]: This operator throws ArrayIndexOutOfBoundsException if using invalid indices.
• map_col[key]: This operator throws NoSuchElementException if key does not exist in map.
• CAST(string_col AS TIMESTAMP): This operator should fail with an exception if the input string can’t be parsed.

SQL Keywords

When spark.sql.ansi.enabled is true, Spark SQL will use the ANSI mode parser. 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. For example, EXPLAIN SELECT ... is a command, but EXPLAIN can be used as identifiers in other places.

When the ANSI mode is disabled, Spark SQL has two kinds of keywords:

• Non-reserved keywords: Same definition as the one when the ANSI mode enabled.
• Strict-non-reserved keywords: A strict version of non-reserved keywords, which can not be used as table alias.

By default spark.sql.ansi.enabled is false.

Below is a list of all the keywords in Spark SQL.