[jira] [Created] (CALCITE-3438) Validator should disallow use of the GROUPING function inside a FILTER clause

["Julian Hyde (Jira)" <ji...@apache.org>]

Julian Hyde created CALCITE-3438:
------------------------------------

             Summary: 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


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)