[jira] [Created] (CALCITE-4100) Join plan with select MAX subquery drops MAX operator

["Sean Broeder (Jira)" <ji...@apache.org>]

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)