Mihai Budiu created CALCITE-6442:
------------------------------------
Summary: Validator rejects FILTER in OVER windows
Key: CALCITE-6442
URL: https://issues.apache.org/jira/browse/CALCITE-6442
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.37.0
Reporter: Mihai Budiu
The Calcite grammar about windows says this:
{code}
windowedAggregateCall:
agg '(' [ ALL | DISTINCT ] value [, value ]* ')'
[ RESPECT NULLS | IGNORE NULLS ]
[ WITHIN GROUP '(' ORDER BY orderItem [, orderItem ]* ')' ]
[ FILTER '(' WHERE condition ')' ]
OVER window
{code}
However, the validator rejects the following query:
{code:sql}
SELECT deptno,
COUNT(DISTINCT deptno) FILTER (WHERE deptno > 10)
OVER win AS agg
FROM emp
WINDOW win AS (PARTITION BY empno)
{code}
with the following error:
{code}
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)
{code}
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.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
