[ https://issues.apache.org/jira/browse/CALCITE-3495?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jin Xing reassigned CALCITE-3495: --------------------------------- Assignee: Wang Yanlin > RelDecorrelator generate plan with different semantics when handle Aggregate > ---------------------------------------------------------------------------- > > Key: CALCITE-3495 > URL: https://issues.apache.org/jira/browse/CALCITE-3495 > Project: Calcite > Issue Type: Bug > Reporter: Jin Xing > Assignee: Wang Yanlin > Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > Given below Sql and data > {code:java} > SELECT deptno FROM dept A where deptno in > (SELECT count(1) FROM emp B where A.deptno = B.deptno) > ----------------------------------------------------- > Data of dept: > deptno > 0 > 1 > ---------------- > Data of emp: > deptno > 1{code} > From the Sql semantics by nest-loop join, we will expect the result as > {code:java} > Result: > deptno > 0 > 1{code} > However the decorrelated plan is as below: > {code:java} > LogicalProject(DEPTNO=[$0]) > LogicalJoin(condition=[=($0, $3)], joinType=[inner]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > LogicalFilter(condition=[=($1, $0)]) > LogicalProject(EXPR$0=[$1], DEPTNO=[$0]) > LogicalAggregate(group=[{0}], EXPR$0=[COUNT()]) > LogicalProject(DEPTNO=[$7], $f0=[1]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > {code} > The result of this plan is as below: > {code:java} > Result: > deptno > 1 > {code} > As we can see the two above results are different. > > -- This message was sent by Atlassian Jira (v8.3.4#803005)