[
https://issues.apache.org/jira/browse/CALCITE-4340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17312688#comment-17312688
]
James Starr commented on CALCITE-4340:
--------------------------------------
[~julianhyde] Thank you for comment. Why does calcite have 2 concurrent ways
of decorrelating queries? I was attempting to due the decorrelation in
SqlToRel and using SubQueryRemoveRule is relatively simpler since it has less
coupling with other things. However, the same problems exists when using
SubQueryRemoveRule. Instead of the blackboard.root being mutated, it is the
top node in the relbuilder. Along with the standing problem correlated nodes
being insufficient to represent the expression generated. I could do the
decorrelation in place, but again this is further complicated by the
decorrelation logic expecting to be rewritten on to an existing node which it
will introspect.
> 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)
