[SPARK-27764] Feature Parity between PostgreSQL and Spark

• Type: Umbrella
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.0.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Xiao Li
• Created: 19/May/19 00:34
• Updated: 12/Dec/22 18:11

Description

PostgreSQL is one of the most advanced open source databases. This umbrella Jira is trying to track the missing features and bugs. 

UPDATED: This umbrella tickets basically intend to include bug reports and general issues for the feature parity. For implementation-dependent behaviours and ANS/SQL standard topics, you need to check the two umbrella below;

• SPARK-30374 Feature Parity between PostgreSQL and Spark (ANSI/SQL)
• SPARK-30375 Feature Parity between PostgreSQL and Spark (implementation-dependent behaviours)

Issue Links

• incorporates: SPARK-26217 Compliance to SQL standard (SQL:2011)
• is related to: SPARK-28696 create database _; allowing in Spark but Hive throws Parse Exception
• is related to: SPARK-29836 PostgreSQL dialect: cast
• relates to: SPARK-30374 Feature Parity between PostgreSQL and Spark (ANSI/SQL)
• relates to: SPARK-30375 Feature Parity between PostgreSQL and Spark (implementation-dependent behaviours)
• relates to: SPARK-27763 Port test cases from PostgreSQL to Spark SQL

Sub-Tasks

• Handling of sublinks within outer-level aggregates.: Open
• List all cases that PostgreSQL throws an exception but Spark SQL is NULL: Open
• support statement using nested joins: Reopened
• Support INTERVAL ... HOUR TO SECOND syntax: Resolved
• Interval type conversion syntax support: Resolved
• Support WITH clause column aliases: Resolved
• Support ORDER BY ... USING syntax: Resolved
• `lag` second argument must be a literal: Open
• Trim the string when cast string type to other types: Resolved
• String concatenation should low priority than other operators: Resolved
• Float/Double type can not accept some special inputs: Resolved
• Order by does not accept a call to rank(): Open
• ntile only accepting positive (>0) values: Open
• Support Interval comparison: Closed
• Date/Timestamp type have different low value and high value with Spark: Open
• Fix substitution order of nested WITH clauses: Resolved
• Date/Time Output Styles and Date Order Conventions: Open
• Handling outer links in CTE subquery expressions: Open
• Evaluation of multiple CTE uses: Resolved
• Decimal can not accept NaN as input: Open
• Decimal precision issue: Resolved
• Decimal can only support precision up to 38: Open
• Correlated scalar subqueries must be aggregated: Resolved
• Division operator support integral division: Resolved
• Add more Date/Time Operators: Open
• SELECT FROM syntax: Open
• Support typed interval expression: Resolved
• Join with USING caluse doesn't hide original tables: Open
• Use Java 8 API to add months to dates: Resolved
• Spark `exclude` always expecting `()`: Open
• Support `TIME` type in Spark: Resolved
• Support accepting the interval keyword in the schema string: Resolved
• Different format when casting interval type to string type: Open
• Pad Decimal numbers with trailing zeros to the scale of the column: Resolved
• Recognize interval as a numeric type: Resolved
• Support recursive view syntax: Resolved
• Formatting dates with negative years: Resolved
• frame bound must be a literal: Open
• Support empty select list: Closed
• not handling usage of group function and window function at some conditions: Resolved
• Support for range frame+row frame in the same query: Resolved
• subqueries must be aggregated before hand: Resolved
• window functions should not be allowed in window definitions: Resolved
• Throw an error on window redefinition: Resolved
• Allow usage of `count` only for parameterless aggregate function: Resolved
• Adds support to `groups` unit type in window clauses: Open
• ORDER BY in aggregate function: Open
• Support limit on recursive queries: Resolved
• [UDF] Supports for alter, rename, owner change should be supported in Spark: Open
• String Constants with C-style Escapes: Open
• char data type should support in Spark: Closed
• Add TimeType to Catalyst: Closed
• Support special date/timestamp values `infinity`/`-infinity`: Open
• Ignore case while resolving time zones: Open
• Support seconds precision by the timestamp type: Open
• DEFAULT option is not supported in Spark: Resolved
• Support interval field values with fractional parts: Resolved
• Make casting strings to intervals case insensitive: Resolved
• Accept strings without `interval` prefix in casting to intervals: Resolved
• Interval strings without explicit unit markings: Open
• Support interval values without INTERVAL clauses: Resolved
• Make `INTERVAL` values comparable: Resolved
• Construct intervals from the `millenniums`, `centuries` or `decades` units: Open
• Support synonyms for interval units: Open
• Support writing `INTERVAL` type to datasource table: Open
• Interval output styles: Open
• Support interval literal with negative sign `-`: Resolved
• Support the optional prefix `@` in interval strings: Open
• Support `ago` in interval strings: Open
• Default year-month units: Resolved
• Support ISO 8601 format for intervals: In Progress
• Precision of the interval type: Open
• Support syntax for zero interval: Open
• Unordered interval units: In Progress
• Thrift in some cases can't parse string to date: Resolved
• Spark should work as PostgreSQL when using + Operator: Resolved
• Make COLUMN optional in ALTER TABLE: Open
• NOT NULL is not supported in Spark: Resolved
• Can't parse '11:00 BST' or '2000-10-19 10:23:54+01' signatures to timestamp: Open
• Spark handles 'NaN' as 0 in sums: Resolved
• Support data insertion in a different order if you wish or even omit some columns in spark sql also like postgresql: Open
• Forcibly create a temporary view in CREATE VIEW if referencing a temporary view: Resolved
• Support typed integer literal expression: Resolved
• Support cascaded syntax in CREATE SCHEMA: Resolved
• Discard a NULL constant in LIMIT: Resolved
• Not allowed to create a permanent view by referencing a temporary view in EXISTS: Resolved
• Some queries with divisions in SQL windows are failling in Thrift: Resolved
• Support divide/multiply for interval types: Resolved
• Dropping columns and changing column names/types are prohibited in VIEW definition: Resolved
• Support sum with interval type values: Reopened
• Make interval type camparable and orderable: Resolved
• Different answers in aggregates of duplicate grouping sets: Resolved
• Support average with interval type values: Reopened
• Whitespaces handling for Cast and BinaryOperation between StringType and NumericTypes: Resolved
• Support grouping function with multiple arguments: Open
• Different answers in nested aggregates with window functions: Open
• Support nested grouping sets: Open
• Resolve group-by columns with integrity constraints: Open
• Support Interval Unit Abbreviations in Interval Literals: Resolved
• Support grouping() in GROUP BY without GroupingSets/Cube/Rollup: Open
• Support the combinations of grouping operations: Open
• Support more expressive forms in GroupingSets/Cube/Rollup: Open
• Support an empty grouping expression: Open
• Trim the string when cast string type to decimals: Resolved
• Support all whitespaces as delimiter for inside a interval value: Resolved
• Interval from year-month/date-time string handling whitespaces: Resolved
• Support nested aggregates: Open
• Improved VALUES support: Open
• Support POSIX Regular Expressions: Open
• Support COMMENT ON syntax: Open
• SQL Datetime util function being casted to double instead of timestamp: Reopened
• Add parameter position for REGEXP_REPLACE: Resolved
• support drop multiple functions: Open
• Support for INET data type: Resolved
• Improve the error when Lead and Lag are not allowed to specify window frame: Resolved
• Simplify offset window function(Remove direction field): Resolved