[jira] [Commented] (CALCITE-2948) SqlToRelConverter generates complicated logical plan for in subquery with non-equi condition
[
https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16827073#comment-16827073
]
Haisheng Yuan commented on CALCITE-2948:
What about $b % 2, CASE WHEN...? Changing pattern will only make things more
complicated, let's not do that way.
> SqlToRelConverter generates complicated logical plan 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: 1.5h
> 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)
[jira] [Commented] (CALCITE-2948) SqlToRelConverter generates complicated logical plan for in subquery with non-equi condition
[
https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16826331#comment-16826331
]
Haisheng Yuan commented on CALCITE-2948:
[~danny0405] For the following query,
{code:java}
select deptno from dept d where deptno in (select deptno from EMP where
empno=d.deptno+1);
{code}
The patch still generates complicated plan.
> SqlToRelConverter generates complicated logical plan 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 20m
> 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)
[jira] [Commented] (CALCITE-2948) SqlToRelConverter generates complicated logical plan for in subquery with non-equi condition
[
https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16822343#comment-16822343
]
Danny Chan commented on CALCITE-2948:
-
[~hyuan] yes, i add `testRemoveSemiJoin1()` and `testRemoveSemiJoin2()` in
RelOptRulesTest.
> SqlToRelConverter generates complicated logical plan 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: 10m
> 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)
[jira] [Commented] (CALCITE-2948) SqlToRelConverter generates complicated logical plan for in subquery with non-equi condition
[
https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16822278#comment-16822278
]
Haisheng Yuan commented on CALCITE-2948:
[~danny0405] Did you try
checkSubQuery(sql).withDecorrelation(false).withLateDecorrelation(true) with
your fix?
> SqlToRelConverter generates complicated logical plan 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: 10m
> 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)
[jira] [Commented] (CALCITE-2948) SqlToRelConverter generates complicated logical plan for in subquery with non-equi condition
[
https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16820669#comment-16820669
]
Haisheng Yuan commented on CALCITE-2948:
The EMPNO is primary key of table emp, so it is already distinct, no aggregate
is needed. For columns that are not distinct, aggregate will be added. So this
is expected.
> SqlToRelConverter generates complicated logical plan 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: 10m
> 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)
[jira] [Commented] (CALCITE-2948) SqlToRelConverter generates complicated logical plan for in subquery with non-equi condition
[
https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16820661#comment-16820661
]
Danny Chan commented on CALCITE-2948:
-
[~vvysotskyi] The aggregate group set keys are already added in the SemiJoin
condition, see [1]. [1]
https://github.com/apache/calcite/blob/b03cdc486cf5c7232bbc6fa9b5f02f564e9601c3/core/src/main/java/org/apache/calcite/rel/rules/SemiJoinRule.java#L119
> SqlToRelConverter generates complicated logical plan 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: 10m
> 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)
[jira] [Commented] (CALCITE-2948) SqlToRelConverter generates complicated logical plan for in subquery with non-equi condition
[
https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16819972#comment-16819972
]
Volodymyr Vysotskyi commented on CALCITE-2948:
--
[~danny0405], the aggregation for these cases guarantees that the resulting
rows number will be not greater than the row number of the outer table, since
without aggregate it may happen for the case when joining columns with repeated
values. Could you please check whether this case works correctly with your fix?
> SqlToRelConverter generates complicated logical plan 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: 10m
> 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)
[jira] [Commented] (CALCITE-2948) SqlToRelConverter generates complicated logical plan for in subquery with non-equi condition
[
https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16819927#comment-16819927
]
Danny Chan commented on CALCITE-2948:
-
Hi [~hyuan] [~zabetak] [~julianhyde] [~vgarg] I have applied a
[PR#1169|[https://github.com/apache/calcite/pull/1169]]
The main diff it to add 2 rules in post-decorrelation phrase:
SemiJoinRule.PROJECT and SemiJoinRemoveRule.PROJECT.
The original plan after decorrelation is:
{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}
The plan is very same with what SubQueryRemoveRule produces. To reduce the
right agg of the above join, we can use rule SemiJoinRule.PROJECT[1], then plan
will change to:
{code:java}
LogicalProject(DEPTNO=[$7])
SemiJoin(condition=[AND(=($0, $10), =($7, $9))], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
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}
Add after applying SemiJoinRemoveRule.PROJECT will get the final plan:
{code:java}
LogicalProject(DEPTNO=[$7])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalJoin(condition=[=($0, +($9, 1))], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, EMP]]){code}
There are 2 points that i want to discuss:
1. The new added rule SemiJoinRemoveRule.PROJECT and SemiJoinRemoveRule.Join
only match both the joins left sides as a TableScan, which i think could be
more common, e.g. a RelNode, but i only decide if the 2 are equals with each
other with the node's digest, i'm not very confident if i change to RelNode it
would be still correct.
2. In order to remove the above join, the join keys of it must all be unique,
or the expanded join rows of same keys would be lost. But now when we use
SubqueryRemoveRule or RelDecorrelator to decorrelate the in subquery, this info
is still ignored when we build a join(the join rows would swell), so i ignore
the uniqueniess either as default for SemiJoinRemoveRule, but i still add a
flag in it to let user control the behavior.
[1]
https://github.com/apache/calcite/blob/b03cdc486cf5c7232bbc6fa9b5f02f564e9601c3/core/src/main/java/org/apache/calcite/rel/rules/SemiJoinRule.java#L121
> SqlToRelConverter generates complicated logical plan 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: 10m
> 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)
[jira] [Commented] (CALCITE-2948) SqlToRelConverter generates complicated logical plan for in subquery with non-equi condition
[
https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16819916#comment-16819916
]
Danny Chan commented on CALCITE-2948:
-
Hi [~hyuan] [~zabetak] [~julianhyde] [~vgarg] I have applied a
[PR#1169|[https://github.com/apache/calcite/pull/1169]]
The main diff it to add 2 rules in post-decorrelation phrase:
SemiJoinRule.PROJECT and SemiJoinRemoveRule.PROJECT.
The original plan after decorrelation is:
{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}
The plan is very same with what SubQueryRemoveRule produces. To reduce the
right agg of the above join, we can use rule SemiJoinRule.PROJECT[1], then plan
will change to:
{code:java}
LogicalProject(DEPTNO=[$7])
SemiJoin(condition=[AND(=($0, $10), =($7, $9))], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
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}
Add after applying SemiJoinRemoveRule.PROJECT will get the final plan:
{code:java}
LogicalProject(DEPTNO=[$7])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalJoin(condition=[=($0, +($9, 1))], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, EMP]]){code}
There are 2 points that i want to discuss:
1. The new added rule SemiJoinRemoveRule.PROJECT and SemiJoinRemoveRule.Join
only match both the joins left sides as a TableScan, which i think could be
more common, e.g. a RelNode, but i only decide if the 2 are equals with each
other with the node's digest, i'm not very confident if i change to RelNode it
would be still correct.
2. In order to remove the above join, the join keys of it must all be unique,
or the expanded join rows of same keys would be lost. But now when we use
SubqueryRemoveRule or RelDecorrelator to decorrelate the in subquery, this info
is still ignored when we build a join(the join rows would swell), so i ignore
the uniqueniess either as default for SemiJoinRemoveRule, but i still add a
flag in it to let user control the behavior.
[1]
https://github.com/apache/calcite/blob/b03cdc486cf5c7232bbc6fa9b5f02f564e9601c3/core/src/main/java/org/apache/calcite/rel/rules/SemiJoinRule.java#L121
> SqlToRelConverter generates complicated logical plan 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: 10m
> 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)
[jira] [Commented] (CALCITE-2948) SqlToRelConverter generates complicated logical plan for in subquery with non-equi condition
[
https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16819700#comment-16819700
]
Haisheng Yuan commented on CALCITE-2948:
I think this is still equi condition. I am just expecting an additional project
field of e.empno+1 on the outer relation.
> SqlToRelConverter generates complicated logical plan 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: sub-query
>
> 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)
