[
https://issues.apache.org/jira/browse/CALCITE-4340?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
James Starr updated CALCITE-4340:
---------------------------------
Description:
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}
was:
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.
> 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: 20m
> 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)
