[jira] [Commented] (CALCITE-770) LogicalAggregate plan node looks incorrect when window functions are present

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

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

Julian Hyde commented on CALCITE-770:
-------------------------------------

Yes, the plan needs to be

{noformat}
LogicalProject(EXPR$0=[$2], EXPR$1=[$3], EXPR$2=[RANK() OVER (ORDER BY $0 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)], EXPR$3=[SUM($1) OVER (ORDER BY $0 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)])
  LogicalAggregate(group=[{0, 1}], EXPR$0=[MIN($1)], EXPR$1=[MAX($1)])
    LogicalProject(JOB=[$2], SAL=[$5])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{noformat}

The bug is in SqlToRelConverter. When creating an Aggregate and looking for aggregate functions, it needs to ignore windowed aggregate functions.

Add tests to SqlToRelConverterTest, and add some queries to winagg.oq. Add a test where this query is the LHS of a join; if there are spurious columns, the offsets to the RHS of the join will be wrong. Also add a test of a winagg on an agg on a join, and a query with winagg, agg, and HAVING clause.

> LogicalAggregate plan node looks incorrect when window functions are present
> ----------------------------------------------------------------------------
>
>                 Key: CALCITE-770
>                 URL: https://issues.apache.org/jira/browse/CALCITE-770
>             Project: Calcite
>          Issue Type: Bug
>    Affects Versions: 1.3.0-incubating
>            Reporter: Aman Sinha
>            Assignee: Julian Hyde
>
> For the following query:
> {code}
>   select min(sal), max(sal), 
>       rank() over (order by job), 
>       sum(sal) over (order by job) 
>     from emp 
>   group by job, sal
> {code}
> I would have expected the LogicalAggregate to do the group-by and produce the MIN and MAX only.  The plan below shows 2 additional fields: agg#2=RANK and agg#3=SUM($1) .  I think this is incorrect because these functions should be associated with the LogicalWindow node only (which does not show up in the plan yet).   Converting the LogicalAggregate to a physical plan node creates difficulties due to this.  Is the physical aggregate supposed to only look at EXPR$1 and EXPR$2 and ignore agg#1, agg#2 ? 
> {code}
> LogicalProject(EXPR$0=[$2], EXPR$1=[$3], EXPR$2=[RANK() OVER (ORDER BY $0 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)], EXPR$3=[SUM($1) OVER (ORDER BY $0 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)])
>   LogicalAggregate(group=[{0, 1}], EXPR$0=[MIN($1)], EXPR$1=[MAX($1)], agg#2=[RANK()], agg#3=[SUM($1)])
>     LogicalProject(JOB=[$2], SAL=[$5])
>       LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}



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