Presto: first_value and last_value do not support ignore nulls and respect nulls

[maciejgrzybek]

select last_value(x) ignore nulls over() from (values(2),(null)) as t(x)

Should return two rows with value 2 but fails on parsing instead:

Query 20160805_170231_00039_7py94 failed: line 1:29: mismatched input 'nulls' expecting {<EOF>, ',', 'FROM', 'WHERE', 'GROUP', 'ORDER', 'HAVING', 'LIMIT', 'APPROXIMATE', 'UNION', 'EXCEPT', 'INTERSECT'}
select last_value(x) ignore nulls over() from (values(2),(null)) as t(x)

If removed ignore nulls it falls to default of respect nulls.

Standard says (4.16.3 of ISO/IEC 9075-2:2011(E)):

The first-value and last-value functions take an arbitrary <value expression> VE as an argument and, for each row R of a windowed table, return the value of VE evaluated on the first row of the window frame of R (for the first-value function) or the last row of the window frame of R (for the last_value function) defined by a window structure descriptor. In addition, RESPECT NULLS or IGNORE NULLS can be specified to indicate whether the rows for which VE evaluates to the null value are preserved or eliminated.

Additionally, section 6.10 of the same document specifies syntax as follows:

<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

[ptkool]

@maciejgrzybek I am putting a PR together for https://github.com/prestodb/presto/issues/4554. I can take a look at this as well.

[maciejgrzybek]

@ptkool Feel free to work on this!

[andtrott]

@ptkool any updates here?

[evgenia82]

IGNORE NULLS still doesn't work and gives me the following error:

I wish someone correct the error. Also, please let me know any workarounds to use in Presto.

[rongrong]

IGNORE NULLS still doesn't work and gives me the following error:

I wish someone correct the error. Also, please let me know any workarounds to use in Presto.

Can you share your query?

[rongrong]

Fixed by https://github.com/prestodb/presto/pull/10568

[evgenia82]

Please take a look at my query. I run it in AWS Athena that has Presto underneath.

Please let me know if my issue comes from something else except IGNORE NULLS, which is highly unlikely because when I replace LAST_VALUE() with LAG() the query runs fine.

[rongrong]

The format is last_value(x) IGNORE NULLS. IGNORE NULLS should be after the parenthesis.

[evgenia82]

I put IGNORE NULLS outside of parenthesis but the query still doesn't work.

[evgenia82]

@rongrong Can you please take a look at the query - I put IGNORE NULLS outside of parenthesis and it still doesn't work? Thanks!

[rongrong]

Which version of Presto Athena is on? You probably want to ask Athena not here why your query doesn't work.

[adamelmore]

@evgenia82 reviving this to point out that there's a forum topic related to IGNORE NULLS not working in Athena; would appreciate any upvotes on that thread!
https://forums.aws.amazon.com/thread.jspa?threadID=306856&tstart=0