[ https://issues.apache.org/jira/browse/CALCITE-2936?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Haisheng Yuan updated CALCITE-2936: ----------------------------------- Description: An existential subquery whose inner child is an aggregate with no grouping columns should be simplified to a Boolean constant. Example: {code:java} exists(select sum(i) from X) --> true not exists(select sum(i) from X) --> false {code} Repro: {code:java} @Test public void testExistentialSubquery() { final String sql = "SELECT e1.empno\n" + "FROM emp e1 where exists\n" + "(select avg(sal) from emp e2 where e1.empno = e2.empno )"; sql(sql).decorrelate(true).ok(); } {code} We got plan: {code:java} LogicalProject(EMPNO=[$0]) LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[CAST($9):INTEGER], $f1=[CAST($10):BOOLEAN]) LogicalJoin(condition=[=($0, $9)], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) LogicalProject(EMPNO=[$0], $f0=[true]) LogicalAggregate(group=[{0}], EXPR$0=[AVG($1)]) LogicalProject(EMPNO=[$0], SAL=[$5]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code} The preferred plan should be: {code:java} LogicalProject(EMPNO=[$0]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code} was: An existential subquery whose inner child is an aggregate with no grouping columns should be simplified to a Boolean constant. Example: exists(select sum(i) from X) --> true not exists(select sum(i) from X) --> false Repro: {code:java} @Test public void testExistentialSubquery() { final String sql = "SELECT e1.empno\n" + "FROM emp e1 where exists\n" + "(select avg(sal) from emp e2 where e1.empno = e2.empno )"; sql(sql).decorrelate(true).ok(); } {code} We got plan: {code:java} LogicalProject(EMPNO=[$0]) LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[CAST($9):INTEGER], $f1=[CAST($10):BOOLEAN]) LogicalJoin(condition=[=($0, $9)], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) LogicalProject(EMPNO=[$0], $f0=[true]) LogicalAggregate(group=[{0}], EXPR$0=[AVG($1)]) LogicalProject(EMPNO=[$0], SAL=[$5]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code} The preferred plan should be: {code:java} LogicalProject(EMPNO=[$0]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code} > Existential subquery that has aggregate without grouping key should be > simplified > --------------------------------------------------------------------------------- > > Key: CALCITE-2936 > URL: https://issues.apache.org/jira/browse/CALCITE-2936 > Project: Calcite > Issue Type: New Feature > Reporter: Haisheng Yuan > Priority: Major > > An existential subquery whose inner child is an aggregate with no grouping > columns should be simplified to a Boolean constant. > Example: > {code:java} > exists(select sum(i) from X) --> true > not exists(select sum(i) from X) --> false > {code} > Repro: > {code:java} > @Test public void testExistentialSubquery() { > final String sql = "SELECT e1.empno\n" > + "FROM emp e1 where exists\n" > + "(select avg(sal) from emp e2 where e1.empno = e2.empno )"; > sql(sql).decorrelate(true).ok(); > } > {code} > We got plan: > {code:java} > LogicalProject(EMPNO=[$0]) > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], > SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[CAST($9):INTEGER], > $f1=[CAST($10):BOOLEAN]) > LogicalJoin(condition=[=($0, $9)], joinType=[inner]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) > LogicalProject(EMPNO=[$0], $f0=[true]) > LogicalAggregate(group=[{0}], EXPR$0=[AVG($1)]) > LogicalProject(EMPNO=[$0], SAL=[$5]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > {code} > The preferred plan should be: > {code:java} > LogicalProject(EMPNO=[$0]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > {code} -- This message was sent by Atlassian JIRA (v7.6.3#76005)