[SPARK-30375] Feature Parity between PostgreSQL and Spark (implementation-dependent behaviours)

• Type: Umbrella
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.0.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Takeshi Yamamuro
• Created: 29/Dec/19 00:40
• Updated: 29/Dec/19 00:59

Description

Among the feature parity between Spark/PostgreSQL (SPARK-27764), this umbrella holds issues for the behaviours that some parts of DBMS-like systems (e.g., PostgreSQL, Oracle, MySQL, ...) follow.

Issue Links

• is related to: SPARK-27764 Feature Parity between PostgreSQL and Spark

Sub-Tasks

• Type Casts: expression::type: Closed
• Support ARRAY(sub-SELECT) expressions: Reopened
• Data type: POINT(x, y): Closed
• Array Functions: unnest: Resolved
• Built-in function: generate_series: Closed
• Add built-in Array Functions: array_append: Resolved
• Built-in SQL Function Support - WIDTH_BUCKET: Resolved
• Add the justify_days(), justify_hours() and justify_interval() functions: Resolved
• Hyperbolic Functions: Resolved
• Difference of interval type conversion between SparkSQL and PostgreSQL: Resolved
• Add built-in Array Functions: array_fill: Resolved
• Add built-in Array Functions: array_dims: Resolved
• Support negative length at LEFT/RIGHT SQL functions: Resolved
• Add built-in String Functions: quote_literal: Resolved
• Data Type Formatting Functions: `to_number`: Resolved
• Adds `random()` sql function: Resolved
• Add built-in Array Functions: array_ndims: Resolved
• Add built-in Array Functions: array_length: Resolved
• Add built-in Array Functions: array_append: Resolved
• Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE: Resolved
• Support [CREATE|DROP] RULE - define a new plan rewrite rule: Resolved
• Trim the string when cast string type to booleans: Resolved
• interval `1. second` should be invalid as PostgreSQL: Resolved
• Add built-in Aggregate Functions: string_agg: Resolved
• String Functions: Add support split_part: Resolved
• Binary String Functions: SHA functions: Resolved
• Add make_interval() function: Resolved
• Support ALTER TABLE [relname] SET SCHEMA [dbname]: Resolved
• NULLS FIRST for DESC and NULLS LAST for ASC: Resolved
• Support truncate multiple tables: Resolved
• Support User-defined Types: Resolved
• String Functions: Enhance LPAD/RPAD function: Resolved
• Infinity, -Infinity, NaN should be recognized in a case insensitive manner: Resolved
• Support SELECT statements in VALUES of INSERT INTO: Resolved
• String Functions: Add support other 4 REGEXP functions: Resolved
• Support IS OF (<type>) predicate: Resolved
• Array indexing is 1-based: Resolved
• Ordered-Set Aggregate Functions: Closed
• String Functions: SUBSTRING support regular expression: Resolved
• Support ANSI Aggregate Function: array_agg: Resolved
• String Functions: regexp_replace is not compatible: Open
• String Functions: Enhance TRIM function: Resolved
• String Functions: decode/encode can not accept 'escape' and 'hex' as charset: Open
• String Functions: Add support btrim: Resolved
• Trigonometric Functions: Resolved
• Built-in Mathematical Functions: SCALE: Resolved
• The LOG function using 10 as the base, but Spark using E: Resolved
• Add built-in Array Functions: array_upper: Resolved
• Add built-in Array Functions: element to array concatenation: Open
• Support more Postgres JSON functions: Open
• Data Type Formatting Functions: `to_char`: Resolved
• Add built-in Array Functions: array_cat: Resolved
• Hypothetical-Set Aggregate Functions: Resolved
• String function: string_to_array: Resolved
• System Information Functions: Resolved
• Aggregate Functions for Statistics: Resolved
• Implement boolean aggregates(BOOL_AND, BOOL_OR and EVERY): Resolved
• Support for converting float/double to binary format: Resolved
• Fully support char and varchar types: Closed
• Support RETURNING cause: Resolved
• Caret operator (^) means bitwise XOR in Spark/Hive and exponentiation in Postgres/Redshift: Open
• Spark trunc() func does not support for number group as PostgreSQL: Open
• Implement bitwise integer aggregates(BIT_AND and BIT_OR): Resolved
• Implement ILIKE operator: Closed
• Concat for array in Spark SQL is not the one in PostgreSQL but array_cat: Resolved
• List all built-in UDFs have different names: Resolved
• Support SUBSTRING(str FROM n1 [FOR n2]) syntax: Resolved
• substr returns different values: Resolved
• Support bit string types/literals: Resolved
• Support geometric types: Resolved
• Support TRIM(trimStr FROM str) syntax: Resolved
• SELECT DISTINCT ON ( expression [, ...] ) syntax: Resolved
• Missing escape_string_warning and standard_conforming_strings config: Resolved
• Bankers' rounding for double types: Resolved
• extract support interval type: Resolved
• Copy data between a file and a table: Resolved
• Return seconds with fraction from `date_part`/`extract`: Resolved
• Implement more text pattern operators: Resolved
• Support field synonyms at `extract`: Resolved
• Accept 'on' and 'off' as input for boolean data type: Resolved
• Missing some mathematical operators: Resolved
• Cast numeric to integral type need round: Open
• Support `dow`, `isodow` and `doy` at `extract()`: Resolved
• Table inheritance: Resolved
• Support submit sql content only contains comments.: Open
• Support window frame ORDER BY i ROWS BETWEEN (('foo' < 'foobar')::integer) PRECEDING AND CURRENT ROW: Open