[SPARK-30374] Feature Parity between PostgreSQL and Spark (ANSI/SQL)

• Type: Umbrella
• Status: Resolved
• Priority: Major
• Resolution: Done
• Affects Version/s: 3.0.0
• Component/s: SQL
• Epic Link: ANSI SQL compliance
• Assignee: Apache Spark
• Reporter: Takeshi Yamamuro
• Created: 29/Dec/19 00:13
• Updated: 10/Aug/24 21:01
• Resolved: 10/Aug/24 21:01

Description

Among the feature parity between Spark/PostgreSQL (SPARK-27764), this umbrella holds issues for the ANSI/SQL standard.

Issue Links

• is related to: SPARK-27764 Feature Parity between PostgreSQL and Spark
• relates to: SPARK-35030 ANSI SQL compliance
• relates to: SPARK-33459 Commonly used Teradata extension syntax
• links to: GitHub Pull Request #27152

Sub-Tasks

• Support ANSI SQL Boolean-Predicate syntax: Resolved
• ANSI SQL: NTH_VALUE function: Resolved
• ANSI SQL: LATERAL derived table(T491): Closed
• ANSI SQL: Character literals: Resolved
• ANSI SQL: OVERLAY function(T312): Resolved
• Support ANSI SQL: LIKE ... ESCAPE syntax: Resolved
• ANSI-F053: Overlaps datetimes predicate support: Open
• Support ANSI SQL CREATE SEQUENCE: Open
• Forbid reserved keywords as identifiers when ANSI mode is on: Resolved
• ANSI SQL: Recursive query: In Progress
• Support LOCALTIMESTAMP when ANSI mode enabled: Resolved
• Support CURRENT_TIME and LOCALTIME when ANSI mode enabled: Resolved
• Test both ANSI mode and Spark mode: Open
• ANSI SQL: Nested bracketed comments: Resolved
• ANSI SQL: delete and update does not support in Spark: Resolved
• ANSI SQL: Collation Support: Open
• ANSI SQL: unique predicate: Open
• ANSI SQL: OVERLAY function support byte array(T312): Resolved
• ANSI SQL(E021-05): OCTET_LENGTH function: Resolved
• Support ANSI SQL:SIMILAR TO ... ESCAPE syntax: In Progress
• ANSI SQL: Unicode escapes in literals: Open
• Support ANSI SQL:member predicate: Open
• ANSI SQL: Top-level <result offset clause> in <query expression>: Resolved
• Support Aggregate Expressions with filter: Resolved
• define reserved keywords after SQL standard: Resolved
• Support Filter expression allows simultaneous use of DISTINCT: Resolved
• Support Filter expression uses IN/EXISTS predicate sub-queries: Resolved
• Support Filter expression reference the outer query: Open
• Support dec as shorthand for decimal: Resolved
• SELECT INTO syntax: Open
• adds support for `filter` clause: Resolved
• Support WITH clause in subqueries: Resolved
• DIV support decimal type: Resolved
• Fully support correlation names in the FROM clause: Open
• Real data type is not supported in Spark SQL which is supporting in postgresql: Resolved
• Throw exception on overflow for integers: Resolved
• The exact math method should be called only when there is a corresponding function in Math: Resolved
• Support option to throw exception if overflow occurs during Decimal arithmetic: Resolved
• Spark should support select <columnname> into <table> from <table> where <condition> as PostgreSQL supports: Resolved
• When one or more DISTINCT aggregate expressions operate on the same field, the DISTINCT aggregate expression allows the use of the FILTER clause: Resolved
• When there are multiple DISTINCT aggregate expressions acting on different fields, any DISTINCT aggregate expression allows the use of the FILTER clause: Resolved
• [SQL] ANSI SQL: Throw exception on format invalid and overflow when casting String to IntegerType.: Resolved
• Lead/Lag window function throws AnalysisException without ORDER BY clause: In Progress
• first_value/last_value window function throws ParseException: Resolved
• ANSI SQL: FIRST_VALUE function: Closed
• ANSI SQL: LAST_VALUE function: Closed
• AnalysisException that window RangeFrame not match RowFrame: Resolved
• Spark SQL can't display bracketed comments well in generated golden files: Resolved
• Support (IGNORE | RESPECT) NULLS for LEAD/LAG/NTH_VALUE/FIRST_VALUE/LAST_VALUE: Resolved
• Add documents information for window function.: Resolved
• Support grant/revoke access privileges like postgresql: Open
• Eliminate filter clause in aggregate: Resolved
• Aggregation support aggregate function with multiple foldable expressions.: Resolved
• Improve the performance for NTH_VALUE and Reactor the OffsetWindowFunction: Resolved
• Respect ANSI standard and change FIRST_VALUE as pure window function.: Resolved
• Improve the performance for FIRST_VALUE: Resolved
• Support drop column in spark also like in postgresql: Open
• LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]: Resolved
• ANALYZE command support analyze all tables in a specific database: Resolved
• Improve performance for nth_value ignore nulls over offset window: Resolved
• Improve performance for last_value over unbounded window frame: Resolved
• Improve performance for last_value over unbounded preceding window frame: Resolved
• ANSI SQL: LATERAL derived table(T491): Resolved
• Support the utils for escapse the regex for ANSI SQL: SIMILAR TO ... ESCAPE syntax: In Progress
• Check unclosed bracketed comments: Resolved