[jira] [Commented] (CALCITE-1327) Nested aggregate windowed query fails

[Julian Hyde (JIRA)]

    [ 
https://issues.apache.org/jira/browse/CALCITE-1327?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15397161#comment-15397161
 ] 

Julian Hyde commented on CALCITE-1327:
--------------------------------------

I reviewed the patch again. Looks good. I added some more test cases, and they 
passed.

However I realized there might be a much simpler fix. Take a look at the lines 
in SqlValidatorImpl.registerQuery:

{code}
      if (isAggregate(select)) {
        aggScope =
            new AggregatingSelectScope(selectScope, select, false);
        selectScopes.put(select, aggScope);
      } else {
        selectScopes.put(select, selectScope);
      }
{code}

I have a feeling that {{isAggregate(select)}} is returning false for, say, 
{{select avg(sum(sal)) over (partition by empno) from emp}}, but it should be 
returning true. And if it did, your big new {{else}} block is not necessary.

Can you please take a look at fixing {{isAggregate}}?

By the way, here are the new tests:

{code}
    sql("select avg(sum(sal)) over ^(partition by empno)^ from emp")
        .fails("Expression 'EMP.EMPNO' is not being grouped");

    sql("select avg(sum(sal)) over (partition by ^empno^)\n"
        + "from emp group by deptno")
        .fails("Expression 'EMPNO' is not being grouped");

    sql("select avg(sum(sal)) over (partition by deptno order by ^empno^)\n"
        + "from emp group by deptno")
        .fails("Expression 'EMPNO' is not being grouped");

    // expression is OK
    sql("select avg(sum(sal)) over (partition by 10 - deptno\n"
        + "   order by deptno / 2 desc)\n"
        + "from emp group by deptno").ok();

    // expression involving non-GROUP column is not OK
    sql("select avg(sum(sal)) over (partition by deptno + ^empno^)\n"
        + "from emp group by deptno")
        .fails("Expression 'EMPNO' is not being grouped");

    sql("select avg(sum(sal)) over (partition by empno + deptno)\n"
        + "from emp group by empno + deptno").ok();

    sql("select avg(sum(sal)) over (partition by empno + deptno + 1)\n"
        + "from emp group by empno + deptno").ok();

    sql("select avg(sum(sal)) over (partition by ^deptno^ + 1)\n"
        + "from emp group by empno + deptno")
        .fails("Expression 'DEPTNO' is not being grouped");

    sql("select avg(empno + deptno) over (partition by empno + deptno + 1),\n"
        + "  count(empno + deptno) over (partition by empno + deptno + 1)\n"
        + "from emp group by empno + deptno").ok();
{code}

> Nested aggregate windowed query fails
> -------------------------------------
>
>                 Key: CALCITE-1327
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1327
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Gautam Kumar Parai
>            Assignee: Gautam Kumar Parai
>
> Regression from CALCITE-750 Allow nested window aggregates. 
> Calcite allows illegal queries instead of raising an appropriate error. When 
> executing the following query calcite does not raise the following error.
> {code}
> select avg(sum(b)) over (partition by b) from t1;
> ERROR:  Expression 'b' is not being grouped
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)