[
https://issues.apache.org/jira/browse/CALCITE-6442?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17856099#comment-17856099
]
Julian Hyde commented on CALCITE-6442:
--------------------------------------
Can you see who added it to the grammar, and as part of which change? I don’t
recall this ever being supported, and apparently there are no tests.
I’m not even sure of the desired semantics. If it is “5 ROWS PRECEDING”, is the
filter applied before or after determining the previous 5 rows?
> 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
> Priority: Minor
>
> 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)
