[ https://issues.apache.org/jira/browse/CALCITE-3605?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Wang Yanlin reassigned CALCITE-3605: ------------------------------------ Assignee: Wang Yanlin > 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 > Assignee: Wang Yanlin > Priority: Major > > 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)