Implement IGNORE NULLS for window functions about arrow-datafusion HOT 8 CLOSED

Nice, everything is done, so closing it.
thanks @mustafasrepo @huaxingao @viirya @alamb @ozankabak

from arrow-datafusion.

@sunchao @viirya cc

from arrow-datafusion.

@alamb are we okay to support such syntax in DF?

select first_value(a) ignore nulls over (partition by id order by id) from (select 1 id, 'a' a union all select 1 id, 'b' a union all select 1 id, null); 

Spark/Oracle supports it fully
PG doesn't support such syntax at all.
DuckDB supports slightly different syntax

select first_value(a ignore nulls) over (partition by id order by id) from (select 1 id, 'a' a union all select 1 id, 'b' a union all select 1 id, null); 

from arrow-datafusion.

It makes sense to me -- maybe @ozankabak or @mustafasrepo have some thoughts in this area as well

from arrow-datafusion.

Note that looks like Spark syntax follows SQL 2016 standard:

https://github.com/ronsavage/SQL/blob/19215adc8639d031a44acad7873c209444b71f1f/sql-2016.ebnf#L1397

lead_or_lag_function ::=
  lead_or_lag left_paren lead_or_lag_extent
      ( comma offset ( comma default_expression )? )? right_paren
      null_treatment?
...
null_treatment ::=
  RESPECT NULLS | IGNORE NULLS

I don't find this syntax in SQL 2003.

from arrow-datafusion.

Thanks @viirya
Looks like currently the DF is able to parse the SQL 2016 standard syntax for this specific case, so it is probably worth to try implementing IGNORE NULLS support

@mustafasrepo cc
if you have any opinions on that?

from arrow-datafusion.

It seems there is no defined opinion yet, I'll create a PR and we can discuss in details

from arrow-datafusion.

Thanks @viirya Looks like currently the DF is able to parse the SQL 2016 standard syntax for this specific case, so it is probably worth to try implementing IGNORE NULLS support

@mustafasrepo cc if you have any opinions on that?

I would be nice to have this feature given that we have parsing support already, and it is a standart feature. Thanks @comphead for bringing this up.

from arrow-datafusion.