[CALCITE-5863] Calcite rejects valid query with multiple ORDER BY columns and constant RANGE bounds in window functions - ASF JIRA

XML

Word

Printable

JSON

Usually, it is not valid to specify multiple ORDER BY columns with RANGE bounds in window functions:

SELECT sum(x) OVER (ORDER BY x,y RANGE BETWEEN 3 preceding and 4 following) from t;

However, it is valid if both bounds are "constant" bound (CURRENT ROW, UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING), i.e:

SELECT sum(x) OVER (ORDER BY x,y RANGE BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) from t;

(tested on PostgreSQL and SQL Server)
Calcite will incorrectly reject it.

links to

GitHub Pull Request #3325