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

Julian Hyde commented on CALCITE-2158:
--------------------------------------

Thanks for all the detective work. I believe the modern approach – namely, 
{{SqlToRelConverter.Config.isExpand()}} is false, and use 
{{SubQueryRemoveRule}} – is better and has fewer bugs. So I recommend using 
that, and would prioritize fixing the bugs in that code path.

> SubQuery with EXISTS clause creates redundant aggregate call
> ------------------------------------------------------------
>
>                 Key: CALCITE-2158
>                 URL: https://issues.apache.org/jira/browse/CALCITE-2158
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Volodymyr Vysotskyi
>            Assignee: Julian Hyde
>            Priority: Major
>
> When {{SqlToRelConverter.Config.isExpand()}} returns true, subqueries are 
> expanded in {{SqlToRelConverter}}.
> Then for the queries, like this:
> {code:sql}
> SELECT cs1.sal
> FROM emp cs1
> WHERE EXISTS
>     (SELECT *
>      FROM emp cs2
>      WHERE cs1.sal = cs2.sal
>        AND cs1.deptno <> cs2.deptno)
> {code}
> Calcite returns logical plan with excessive aggregate calls:
> {noformat}
> LogicalProject(SAL=[$5])
>   LogicalFilter(condition=[IS NOT NULL($9)])
>     LogicalCorrelate(correlation=[$cor0], joinType=[left], 
> requiredColumns=[{5, 7}])
>       LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>       LogicalAggregate(group=[{}], agg#0=[MIN($0)])
>         LogicalProject($f0=[true])
>           LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], 
> HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
>             LogicalFilter(condition=[AND(=($cor0.SAL, $5), <>($cor0.DEPTNO, 
> $7))])
>               LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {noformat}
> But when {{SqlToRelConverter.Config.isExpand()}} returns false and 
> SubQueryRemoveRule rules are applied to the logical plan with RexSubQuery, 
> the resulting logical plan is correct and does not contain excessive 
> aggregate calls:
> {noformat}
> LogicalProject(SAL=[$5])
>   LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], 
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
>     LogicalCorrelate(correlation=[$cor0], joinType=[inner], 
> requiredColumns=[{5, 7}])
>       LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>       LogicalAggregate(group=[{0}])
>         LogicalProject(i=[true])
>           LogicalFilter(condition=[AND(=($cor0.SAL, $5), <>($cor0.DEPTNO, 
> $7))])
>             LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {noformat}
> These cases may be observed using this unit test:
> {code:java}
>   @Test public void testExistsExpand() {
>     final HepProgram preProgram = HepProgram.builder()
>         .addRuleInstance(SubQueryRemoveRule.FILTER)
>         .addRuleInstance(SubQueryRemoveRule.PROJECT)
>         .addRuleInstance(SubQueryRemoveRule.JOIN)
>         .build();
>     final HepProgram program = HepProgram.builder()
>         .build();
>     final String sql = "SELECT cs1.sal\n"
>         + "FROM     emp cs1 \n" 
>         + "WHERE    EXISTS\n" 
>         + "(SELECT *\n" 
>         + "FROM   emp cs2\n" 
>         + "WHERE  cs1.sal = cs2.sal\n" 
>         + "AND    cs1.deptno <> cs2.deptno)";
>     sql(sql)
>         .withDecorrelation(false)
>         .withTrim(false)
>         .expand(true) // change to false
>         .withPre(preProgram)
>         .with(program)
>         .checkUnchanged();
>   }
> {code}



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Reply via email to