[
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)
