[
https://issues.apache.org/jira/browse/CALCITE-1494?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15881763#comment-15881763
]
Vineet Garg edited comment on CALCITE-1494 at 2/24/17 1:48 AM:
---------------------------------------------------------------
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.
Note that my fix is on top of your 1494-val-gen changes.
was (Author: vgarg):
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)
