[
https://issues.apache.org/jira/browse/CALCITE-4340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17312697#comment-17312697
]
Julian Hyde commented on CALCITE-4340:
--------------------------------------
{quote}Why does calcite have 2 concurrent ways of decorrelating queries?{quote}
One is older than the other. The old way (during SqlToRel conversion) worked,
but was difficult to extend. At some point the new way probably became
superior, but we have never checked.
And by the way, there are two distinct problems to solve: converting scalar
sub-queries (which includes IN, EXISTS, and scalar sub-queries as used in the
SELECT clause), and decorrelating queries. I don't remember whether the two
approaches perform decorrelation in the same way.
> 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)
