[CALCITE-4100] Join plan with select MAX subquery drops MAX operator

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.23.0
• Component/s: core
• Assignee: Unassigned
• Reporter: Sean Broeder
• Created: 01/Jul/20 22:33
• Updated: 16/Sep/20 08:35

Description

The following SQL query can be used to demonstrate the issue

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

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

 

Issue Links

• is related to: CALCITE-4210 Sub-query in ON clause is not correctly expanded by SqlToRelConverter