[
https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16806335#comment-16806335
]
Danny Chan commented on CALCITE-2948:
-------------------------------------
[~julianhyde] [~hyuan] [~vgarg] The value generator for the first join is right
for the scope, it join on the correlate variables with the outer view[1] and
make a distinct agg to remove duplicates. The second join is kind of like a dim
join which supplement the remaining fields of the outer view[2]. So, we can
only simplify the plan if:
a. It is a semi-join, for this case, it is an in subquery.
b. The outer most projection must only project the fields which are among the
correlated variables.
c. The outer most projection fields must be unique in the outer view (primary
key or unique keys), this must be satisfied cause the first join will make
cartesian product on the correlate condition, and that is the reason why we
must make a distinct agg. If the outer view do not project on unique fields, we
can not remove the second join.
For a when we rewrite in clause for the query plan in SubQueryRemoveRule[3],
the join type passed in is JoinRelType.INNER, and the constructed
LogicalCorrelate is also with SemiJoinType.INNER, So we do not have any info to
know the LogicalCorrelate is a semi-join when we do[2].
For b and c i think we should do it in PlannerRule and query meta in
MetaDataQuery.
[1]
https://github.com/apache/calcite/blob/d7946a94adfd2e788f5d324910944dd65dab11ee/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java#L1040
[2]
https://github.com/apache/calcite/blob/d7946a94adfd2e788f5d324910944dd65dab11ee/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java#L1187
[3]
https://github.com/apache/calcite/blob/d7946a94adfd2e788f5d324910944dd65dab11ee/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java#L411
> SqlToRelConverter generates complicated logical plan for subquery
> -----------------------------------------------------------------
>
> 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)
