Documentation on Windowed aggregation does not describe limitations

1

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
2

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