[
https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16828872#comment-16828872
]
Danny Chan edited comment on CALCITE-2948 at 4/29/19 2:03 AM:
--------------------------------------------------------------
[~hyuan] Thx for your review
SubqueryRemoveRule and RelDecorrelator actually do the same thing. I still
think that we should generate simpler plan at the very first time, removing a
join node is much more complex and has many constraints like what i have list
above.
Can you give some example how to decorrelate with condition a > $cor1 or a =
func($cor1) ?
BTW, why you think the plan for testInSubqueryWithNonEqualCondition4 is wrong,
the new plan is equivalent to:
{code:java}
select dept.deptno from dept
join (select distinct deptno as dd, empno -1 as ee
from emp
where dd = ee) eee
on dept.deptno = eee.dd{code}
was (Author: danny0405):
[~hyuan] Thx for your review
SubqueryRemoveRule and RelDecorrelator actually do the same thing. I still
think that we should generate simpler plan at the very first time, removing a
join node is much more complex and has many constraints like what i have list
above.
Can you give some example how to decorrelate with condition a > $cor1 or a =
func($cor1) ?
BTW, why you think the plan for testInSubqueryWithNonEqualCondition4 is wrong,
the new plan is equivalent to:
{code:java}
select dept.deptno from dept
join (select distinct deptno as dd, empno -1 as ee
from emp
where dd = ee){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)
