Wang Yanlin created CALCITE-3605: ------------------------------------ Summary: Semantics of relation operator changed after decorrelated Key: CALCITE-3605 URL: https://issues.apache.org/jira/browse/CALCITE-3605 Project: Calcite Issue Type: Bug Reporter: Wang Yanlin
For sql {code:java} final String sql = "select deptno from (select deptno, empno from emp) p\n" + "where empno in\n" + "(select count(*) from dept where p.deptno = dept.deptno)"; {code} before decorrelated, the relnode tree is {code:java} LogicalProject(DEPTNO=[$0]) LogicalProject(DEPTNO=[$0], EMPNO=[$1]) LogicalFilter(condition=[=($1, $2)]) LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}]) LogicalProject(DEPTNO=[$7], EMPNO=[$0]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalAggregate(group=[{}], EXPR$0=[COUNT()]) LogicalProject($f0=[0]) LogicalFilter(condition=[=($cor0.DEPTNO, $0)]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) {code} after decorrelatd, the relnode tree is {code:java} LogicalProject(DEPTNO=[$0]) LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[inner]) LogicalProject(DEPTNO=[$7], EMPNO=[$0]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalAggregate(group=[{0}], EXPR$0=[COUNT()]) LogicalProject(DEPTNO=[$0], $f0=[0]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) {code} however, these two relnode trees have different semantics. Assume, the data in dept and emp is as below {code:java} EMP (deptno, empno) 3, 6 10, 1 8, 0 DEPT (deptno, name) 3, "a" 10, "b" {code} The output of the sql should be +------+ |10| |8| +------+ but the output of the relnode after decorrelated is +------+ |10| +------+ -- This message was sent by Atlassian Jira (v8.3.4#803005)