[
https://issues.apache.org/jira/browse/CALCITE-4100?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rui Wang updated CALCITE-4100:
------------------------------
Description:
The following SQL query can be used to demonstrate the issue
{code:sql}
// Some comments here
select e.empno, e.sal, e.deptno emp_dept, d.deptno dep_dept
from emp e
left join
dept d
on e.deptno = (
select max(sal)
from emp
where deptno = e.deptno)
{code}
The above query generates the following plan which does not contain the MAX
operator
{code:java}
LogicalProject(EMPNO=[$0], SAL=[$5], EMP_DEPT=[$7], DEP_DEPT=[$9])
LogicalJoin(condition=[=($0, $7)], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{code}
However, if I just issue the subquery with the max operation I do get a plan
with the MAX operator.
select max(sal) from emp
This query generates the following plan
{code:java}
LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
LogicalProject(SAL=[$5])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}
was:
The following SQL query can be used to demonstrate the issue
{code:sql}
// Some comments here
select e.empno, e.sal, e.deptno emp_dept, d.deptno dep_dept
from emp e
left join
dept d
on e.deptno = (
select max(sal)
from emp
where deptno = e.deptno)
{code}
The above query generates the following plan which does not contain the MAX
operator
LogicalProject(EMPNO=[$0], SAL=[$5], EMP_DEPT=[$7], DEP_DEPT=[$9])
LogicalJoin(condition=[=($0, $7)], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
However, if I just issue the subquery with the max operation I do get a plan
with the MAX operator.
select max(sal) from emp
This query generates the following plan
LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
LogicalProject(SAL=[$5])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> Join plan with select MAX subquery drops MAX operator
> ------------------------------------------------------
>
> Key: CALCITE-4100
> URL: https://issues.apache.org/jira/browse/CALCITE-4100
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.23.0
> Reporter: Sean Broeder
> Priority: Major
>
> The following SQL query can be used to demonstrate the issue
> {code:sql}
> // Some comments here
> select e.empno, e.sal, e.deptno emp_dept, d.deptno dep_dept
> from emp e
> left join
> dept d
> on e.deptno = (
> select max(sal)
> from emp
> where deptno = e.deptno)
> {code}
> The above query generates the following plan which does not contain the MAX
> operator
> {code:java}
> LogicalProject(EMPNO=[$0], SAL=[$5], EMP_DEPT=[$7], DEP_DEPT=[$9])
> LogicalJoin(condition=[=($0, $7)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> However, if I just issue the subquery with the max operation I do get a plan
> with the MAX operator.
> select max(sal) from emp
> This query generates the following plan
> {code:java}
> LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
> LogicalProject(SAL=[$5])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
