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