Sean Broeder created CALCITE-4100:
-------------------------------------
Summary: 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
The following SQL query can be used to demonstrate the issue
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
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]])
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
