[
https://issues.apache.org/jira/browse/CALCITE-3605?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16997762#comment-16997762
]
Wang Yanlin commented on CALCITE-3605:
--------------------------------------
{code:java}
// RelOptRulesTest
@Test public void testDecorrelateIn() throws Exception {
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)";
checkSubQuery(sql).withLateDecorrelation(true).check();
}
{code}
and the plan is
{code:java}
<TestCase name="testDecorrelateIn">
<Resource name="sql">
<![CDATA[select deptno from (select deptno, empno from emp) p
where empno in
(select count(*) from dept where p.deptno = dept.deptno)]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
LogicalProject(DEPTNO=[$0])
LogicalFilter(condition=[IN($1, {
LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
LogicalProject($f0=[0])
LogicalFilter(condition=[=($cor0.DEPTNO, $0)])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
})], variablesSet=[[$cor0]])
LogicalProject(DEPTNO=[$7], EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
<Resource name="planMid">
<![CDATA[
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]])
]]>
</Resource>
<Resource name="planAfter">
<![CDATA[
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]])
]]>
</Resource>
</TestCase>
{code}
> 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)
