[jira] [Commented] (CALCITE-1726) Subquery in FILTER is left untransformed

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

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

Vineet Garg commented on CALCITE-1726:
--------------------------------------

I took a look into this. Issue is that planner (hepplanner) executes subquery rules (FILTER, PROJECT, JOIN) one by one. Rule Subquery:Project is invoked first, this rule doesn't match because top RexSubquery node (corresponding to FILTER) has yet to be removed/flattened. Next Subquery:Filter is invoked which transforms the top RexSubquery node but leaves the bottom RexSubquery node corresponding to Project untransformed.
Fix for this I believe is to create subquery hep planner program with collection of rules instead of one by one.

> Subquery in FILTER is left untransformed 
> -----------------------------------------
>
>                 Key: CALCITE-1726
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1726
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Vineet Garg
>            Assignee: Julian Hyde
>            Priority: Major
>              Labels: sub-query
>
> Reproducer
> Query:
> {code:SQL}
> select * from emp 
>   where empno IN (select (select max(sal) from emp) from dept)
> {code}
> Plan after {{SubqueryRemoveRule}}
> {code:SQL}
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
>   LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
>     LogicalJoin(condition=[=($0, $9)], joinType=[inner])
>       LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>       LogicalAggregate(group=[{0}])
>         LogicalProject(EXPR$0=[$SCALAR_QUERY({
> LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
>   LogicalProject(SAL=[$5])
>     LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> })])
>           LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> As you can notice scalar query in LogicalProject is left as it is



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)