[SPARK-35030] ANSI SQL compliance

• Type: Epic
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.0.0, 3.1.1, 3.2.0
• Component/s: SQL
• Epic Name: ANSI SQL compliance
• Assignee: Apache Spark
• Reporter: Gengliang Wang
• Created: 12/Apr/21 13:26
• Updated: 17/Dec/24 08:43
• Resolved: 12/Dec/23 08:16

Description

Build an ANSI compliant dialect in Spark, for better data quality and easier migration from traditional DBMS to Spark. For example, Spark will throw an exception at runtime instead of returning null results when the inputs to a SQL operator/function are invalid.

The new dialect is controlled by SQL Configuration `spark.sql.ansi.enabled`:

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

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

Full details of this dialect are documented in https://spark.apache.org/docs/latest/sql-ref-ansi-compliance.html.

Note that some ANSI dialect features maybe not from the ANSI SQL standard directly, but their behaviors align with ANSI SQL's style.

Issue Links

• is related to: SPARK-38860 ANSI enhancements in Spark 3.3
• is related to: SPARK-28989 Introduce ANSI SQL Dialect
• is related to: SPARK-33275 ANSI mode: runtime errors instead of returning null on invalid inputs
• is related to: SPARK-33354 New explicit cast syntax rules in ANSI mode
• is related to: SPARK-34246 New type coercion syntax rules in ANSI mode
• is related to: SPARK-38154 Set up a new GA job to run tests with ANSI mode
• is related to: SPARK-30374 Feature Parity between PostgreSQL and Spark (ANSI/SQL)
• is related to: SPARK-35161 Error-handling SQL functions for ANSI SQL mode migration