[jira] [Resolved] (CALCITE-2158) SubQuery with EXISTS clause creates redundant aggregate call

["Volodymyr Vysotskyi (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-2158?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Volodymyr Vysotskyi resolved CALCITE-2158.
------------------------------------------
       Resolution: Won't Fix
    Fix Version/s: 1.18.0

> 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
>             Fix For: 1.18.0
>
>
> 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)