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

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

Volodymyr Vysotskyi created CALCITE-2158:
--------------------------------------------

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


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)