Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-4100

Join plan with select MAX subquery drops MAX operator

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • 1.23.0
    • None
    • core
    • None

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

       

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              sbroeder Sean Broeder
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated: