Documentation on Windowed aggregation does not describe limitations

The documentation provides very basic descriptions of the functions. It does not state the syntax constraints which become apparent via parser errors.

select rnum, c1, c2, c3, sum( c3 ) over(partition by c1 order by c2 , 1) from dbcert.tolap
(org.apache.calcite.runtime.CalciteContextException) At line 1, column 40: Dremio does not currently support order by with ordinals in over clause

select rnum , c1 , c2, sum( c3 ) over (order by ( sum( c3 ) over (partition by c1 )), c1 , c2 , c3 ) from dbcert.tolap
VALIDATION ERROR: ORDER BY expression should not contain OVER clause

select rnum, c1, c2, c3, avg( distinct c3 ) over(partition by c1) from dbcert.tolap
UNSUPPORTED_OPERATION ERROR: DISTINCT for window aggregate functions is not currently supported

select rnum, c1, c2, c3, first_value( c3 ) over(partition by c1 order by c3 rows between unbounded preceding and unbounded following) from dbcert.tolap
UNSUPPORTED_OPERATION ERROR: This type of window frame is currently not supported
etc etc.

1 Like

It seems first_value and last_value are available as windowed aggregation functions but they do not appear in the documentation.