[jira] [Commented] (CALCITE-4340) Correlated Queries in ON clause do not work when referring to left side

["Danny Chen (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17216431#comment-17216431 ] 

Danny Chen commented on CALCITE-4340:
-------------------------------------

What's the relationship of this issue with CALCITE-4210, is it a enhancement ?

> 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)