[jira] [Commented] (CALCITE-1494) Inefficient plan for correlated sub-queries

["Vineet Garg (JIRA)" <ji...@apache.org>]

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

Vineet Garg commented on CALCITE-1494:
--------------------------------------

Hi [~julianhyde],

I have a commit at https://github.com/vineetgarg02/calcite/commit/f3bb8941ac258b49ce88dbe48110ee0bd1c8fb91 which fixes following test failures
* SqlToRelConverterTest.testNestedCorrelationsDecorrelated
* SqlToRelConverterTest.testNestedCorrelationsDecorrelated2
* RelOptRulesTest.testWhereNotInCorrelated
* RelOptRulesTest.testWhereNotInCorrelated2

I am not sure about {{blank.iq}} as I wasn't able to run it. 
Also I see {{org.apache.calcite.test.MaterializationTest.testSubQuery}} is still failing but from what I have looked this is expected and needs a test output update.

Can you take a look at this commit and incorporate into your branch and re-run calcite tests? Let me know if you would like me to upload the patch here.

> Inefficient plan for correlated sub-queries
> -------------------------------------------
>
>                 Key: CALCITE-1494
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1494
>             Project: Calcite
>          Issue Type: Improvement
>          Components: core
>            Reporter: Vineet Garg
>            Assignee: Julian Hyde
>              Labels: sub-query
>             Fix For: 1.12.0
>
>
> For co-related queries such as 
> {noformat} select sal from emp where empno IN (select deptno from dept where emp.job = dept.name) {noformat}
> Calcite generates following plan (SubqueryRemove Rule + Decorrelation) 
> {noformat}
> LogicalProject(SAL=[$5])
>   LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
>     LogicalJoin(condition=[AND(=($2, $10), =($0, $9))], joinType=[inner])
>       LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>       LogicalAggregate(group=[{0, 1}])
>         LogicalProject(DEPTNO=[$0], JOB=[$1])
>           LogicalProject(DEPTNO=[$0], JOB=[$2])
>             LogicalJoin(condition=[=($2, $1)], joinType=[inner])
>               LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
>               LogicalAggregate(group=[{0}])
>                 LogicalProject(JOB=[$2])
>                   LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {noformat}
> As you can notice there is a outer table scan (EMP in this case) to retrieve all distinct values for co-related column (EMP.JOB here), which is then joined with inner table (DEPT). 
> I am not sure why is this step required. After this join Calcite is anyway doing group by to generate all distinct values for correlated and result column (DEPTNO, JOB) which is then joined with outer table. 
> I think the scan + join of outer table with inner table to generate co-rrelated values is un-necessary and is not required.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)