[
https://issues.apache.org/jira/browse/CALCITE-3438?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16966973#comment-16966973
]
Julian Hyde commented on CALCITE-3438:
--------------------------------------
[~danny0405], It is a bug. The validator should catch this. RexToLixTranslator
(which happens during SQL-to-rel time, or later) is too late. The user should
get a GOOD error message, including parser position.
For these purposes, grouping functions are pretty similar to aggregate
functions. And the expression inside FILTER is pretty much in the same scope as
code inside the WHERE clause. So, while the current PR adds mutable state to
AggFinder it is probably not necessary - the existing code path could handle it.
> Validator should disallow use of the GROUPING function inside a FILTER clause
> -----------------------------------------------------------------------------
>
> Key: CALCITE-3438
> URL: https://issues.apache.org/jira/browse/CALCITE-3438
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Priority: Major
> Labels: pull-request-available
> Time Spent: 40m
> Remaining Estimate: 0h
>
> Validator should disallow use of GROUPING inside FILTER. For instance, the
> following query should be invalid (you can paste it into {{agg.iq}} followed
> by '!ok'):
> {code}
> select deptno, sum(sal) filter (where grouping(deptno) = 0)
> from "scott".emp
> group by deptno;
> {code}
> but fails with an internal error:
> {noformat}
> java.sql.SQLException: Error while executing SQL "select deptno, sum(sal)
> filter (where grouping(deptno) = 0)
> from "scott".emp
> group by deptno": cannot translate call GROUPING($t7)
> at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
> ...
> Caused by: java.lang.RuntimeException: cannot translate call GROUPING($t7)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateCall(RexToLixTranslator.java:756)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:730)
> ...
> {noformat}
> If you change "grouping" to "sum" the validator correctly gives the error
> "FILTER must not contain aggregate expression".
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
