[
https://issues.apache.org/jira/browse/CALCITE-4340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17312784#comment-17312784
]
James Starr commented on CALCITE-4340:
--------------------------------------
[~julianhyde], I agree there are 2 seperate problems.
Currently, to rewrite a rex subquery, you need to have a reference to the rel
node the subquery is being applied too. This could result in correlated rel
node be generated. During the decorrelation, the left side of the rel node
might be used in places other than the left side of the top join. The naive
approach of simple rewriting all the sub queries and filters on top of join is
only correct for inner joins. Alternatively, creating a dummy join to be used,
and then performing a rewrite will generate incorrect results when the dummy is
used in any place other than the left side of the top join. Alternatively, if
a correlated variable is only used on side then a the subquery could be
rewritten on that side of the join. However, when correlated variables
reference both side of the join and there is another condition for the join,
calcite can not currently express that condition with correlate nodes.
So while there are 2 separate problems, they are coupled by existing calcite
API.
The following example can not be expressed correctly with existing correlated
node.
{code:sql}
SELECT *
FROM (VALUES (1, 2, 3)) AS T1(C1, C2, C3)
LEFT JOIN (VALUES (4, 5, 6)) AS T2(C4, C5, C6)
ON T1.C1 = T2.C4
OR (T1.C2, T2.C5) IN (
SELECT T3.C7, T3.C8
FROM (VALUES (7, 8, 9, 10)) AS T3(C7, C8, C9, C10)
WHERE T3.C9 = T1.C3 AND T3.C10 = T2.C6
{code}
> Correlated Queries in ON clause do not work when referring to left side
> -----------------------------------------------------------------------
>
> Key: CALCITE-4340
> URL: https://issues.apache.org/jira/browse/CALCITE-4340
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: James Starr
> Priority: Major
> Labels: pull-request-available
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> Changes from CALCITE-4210 does not robustly support correlated queries in ON
> clauses.
> For the short term I am going revert the logic for supporting ON clause and
> throw an exception when correlated queries occur in ON clause.
> Then I will follow up with a fix that will support single subqueries in on
> clauses. This will require fixing the offset in Blackboard.register,
> correctly detect if correlated variable is on the being used and adding a
> rule RelDecorrelator joined decorrelations.
> Given:
> {code:sql}
> SELECT *
> FROM dept
> LEFT JOIN bonus ON bonus.job = (
> SELECT emp.job
> FROM emp
> WHERE emp.deptno = dept.deptno
> )
> {code}
> Should generate
> {code}
> LogicalProject(DEPTNO=[$0], NAME=[$1], ENAME=[$2], JOB=[$3], SAL=[$4],
> COMM=[$5])
> LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{0}]) //requiring dept.DEPTNO as the correlated value
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalFilter(condition=[=($1, $4)]) //This is comparing bonus.job =
> emp.job
> LogicalJoin(condition=[true], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
> LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
> LogicalProject(JOB=[$2])
> LogicalFilter(condition=[=($7, $cor0.DEPTNO)]) //all employees
> for a given department
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> But instead generates:
> {code}
> LogicalProject(DEPTNO=[$0], NAME=[$1], ENAME=[$2], JOB=[$3], SAL=[$4],
> COMM=[$5])
> LogicalJoin(condition=[=($3, $0)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{0}])
> LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
> LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
> LogicalProject(JOB=[$2])
> LogicalFilter(condition=[=($7, $cor0.DEPTNO)])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
