Minor Description
The Calcite grammar about windows says this:
windowedAggregateCall:
agg '(' [ ALL | DISTINCT ] value [, value ]* ')'
[ RESPECT NULLS | IGNORE NULLS ]
[ WITHIN GROUP '(' ORDER BY orderItem [, orderItem ]* ')' ]
[ FILTER '(' WHERE condition ')' ]
OVER window
However, the validator rejects the following query:
SELECT deptno, COUNT(DISTINCT deptno) FILTER (WHERE deptno > 10) OVER win AS agg FROM emp WINDOW win AS (PARTITION BY empno)
with the following error:
org.apache.calcite.sql.validate.SqlValidatorException: OVER must be applied to aggregate function at java.base/jdk.internal.reflect.DirectConstructorHandleAccessor.newInstance(DirectConstructorHandleAccessor.java:67) at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500) at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:484) at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:507) at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:601) at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:948) at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:933) at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5676) at org.apache.calcite.sql.SqlOverOperator.validateCall(SqlOverOperator.java:77) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:6404)
The easy fix would be to remove this from the grammar.
A harder fix would be to implement support for FILTERs in windows, but I don't know how hard that would be.
Attachments
Issue Links
- is related to
-
CALCITE-883 Support RESPECT NULLS, IGNORE NULLS option for LEAD, LAG, FIRST_VALUE, LAST_VALUE and NTH_VALUE window functions
-
- Closed
-