[SPARK-28310] ANSI SQL grammar support: first_value/last_value(expression, [RESPECT NULLS | IGNORE NULLS])

• Type: Improvement
• Status: Reopened
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 3.0.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Zhu, Lipeng
• Created: 09/Jul/19 02:19
• Updated: 16/Nov/20 06:41

Description

According to the ANSI SQL 2011:

<default expression> ::= <value expression>
<null treatment> ::= RESPECT NULLS | IGNORE NULLS
<first or last value function> ::=
<first or last value> <left paren> <value expression> <right paren> [ <null treatment>
]
<first or last value> ::=
FIRST_VALUE | LAST_VALUE

Teradata - https://docs.teradata.com/reader/756LNiPSFdY~4JcCCcR5Cw/SUwCpTupqmlBJvi2mipOaA 

Oracle - https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/FIRST_VALUE.html#GUID-D454EC3F-370C-4C64-9B11-33FCB10D95EC

Redshift – https://docs.aws.amazon.com/redshift/latest/dg/r_WF_first_value.html

 

Postgresql didn't implement the Ignore/respect nulls. 

https://www.postgresql.org/docs/devel/functions-window.html

Note

The SQL standard defines a RESPECT NULLS or IGNORE NULLS option for lead, lag, first_value, last_value, and nth_value. This is not implemented in PostgreSQL: the behavior is always the same as the standard's default, namely RESPECT NULLS.

  

Issue Links

• is duplicated by: SPARK-30726 ANSI SQL: FIRST_VALUE function
• is duplicated by: SPARK-30727 ANSI SQL: LAST_VALUE function
• relates to: SPARK-33459 Commonly used Teradata extension syntax
• links to: GitHub Pull Request #25082
• links to: GitHub Pull Request #27458