[
https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16828857#comment-16828857
]
Haisheng Yuan commented on CALCITE-2948:
----------------------------------------
Well, I found that the plan described in issue can cause wrong result, because
it scans the outer relation twice, which is not allowed if the outer relation
contains volatile function like random().
{code:sql}
@Test public void testWhereInCorrelatedVolatile() {
final String sql = "select deptno\n"
+ "from (select deptno, cast(rand()*empno as integer) empno from EMP)
e\n"
+ "where deptno in (select deptno\n"
+ "from EMP where empno=e.empno+1)";
checkSubQuery(sql).withDecorrelation(false).withLateDecorrelation(true).check();
}
{code}
We get plan:
{code:xml}
<TestCase name="testWhereInCorrelatedVolatile">
<Resource name="planBefore">
<![CDATA[
LogicalProject(DEPTNO=[$0])
LogicalFilter(condition=[IN($0, {
LogicalProject(DEPTNO=[$7])
LogicalFilter(condition=[=($0, +($cor0.EMPNO, 1))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
})], variablesSet=[[$cor0]])
LogicalProject(DEPTNO=[$7], EMPNO=[CAST(*(RAND(), $0)):INTEGER NOT NULL])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
<Resource name="planMid">
<![CDATA[
LogicalProject(DEPTNO=[$0])
LogicalProject(DEPTNO=[$0], EMPNO=[$1])
LogicalFilter(condition=[=($0, $2)])
LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{1}])
LogicalProject(DEPTNO=[$7], EMPNO=[CAST(*(RAND(), $0)):INTEGER NOT
NULL])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}])
LogicalProject(DEPTNO=[$7])
LogicalFilter(condition=[=($0, +($cor0.EMPNO, 1))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
<Resource name="planAfter">
<![CDATA[
LogicalProject(DEPTNO=[$0])
LogicalJoin(condition=[AND(=($1, $3), =($0, $2))], joinType=[inner])
LogicalProject(DEPTNO=[$7], EMPNO=[CAST(*(RAND(), $0)):INTEGER NOT NULL])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0, 1}])
LogicalProject(DEPTNO=[$7], EMPNO0=[$9])
LogicalJoin(condition=[=($0, +($9, 1))], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}])
LogicalProject(EMPNO=[CAST(*(RAND(), $0)):INTEGER NOT NULL])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
{code}
> Complicated logical plan generated for in subquery with non-equi condition
> --------------------------------------------------------------------------
>
> Key: CALCITE-2948
> URL: https://issues.apache.org/jira/browse/CALCITE-2948
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Haisheng Yuan
> Assignee: Danny Chan
> Priority: Major
> Labels: pull-request-available, sub-query
> Time Spent: 1h 40m
> Remaining Estimate: 0h
>
> Repro:
> Add the following test to SqlToRelConverterTest.java.
> {code:java}
> @Test public void testSubQueryIN() {
> final String sql = "select deptno\n"
> + "from EMP e\n"
> + "where deptno in (select deptno\n"
> + "from EMP where empno=e.empno+1)";
> sql(sql).ok();
> }
> {code}
> Plan:
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[AND(=($0, $10), =($7, $9))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0, 1}])
> LogicalProject(DEPTNO=[$7], EMPNO0=[$9])
> LogicalJoin(condition=[=($0, +($9, 1))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalProject(EMPNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> One join would suffice.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
