You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Vineet Garg (JIRA)" <ji...@apache.org> on 2017/03/28 20:37:41 UTC
[jira] [Updated] (CALCITE-1726) Subquery in FILTER is left
untransformed
[ https://issues.apache.org/jira/browse/CALCITE-1726?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Vineet Garg updated CALCITE-1726:
---------------------------------
Description:
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
was:
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
> 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
>
> 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
(v6.3.15#6346)