[CALCITE-4340] Correlated Queries in ON clause do not work when referring to left side

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: core
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: James Starr
• Created: 15/Oct/20 20:49
• Updated: 25/May/21 15:27

Description

Changes from CALCITE-4210 does not robustly support correlated queries in ON clauses.

For the short term I am going revert the logic for supporting ON clause and throw an exception when correlated queries occur in ON clause.

Then I will follow up with a fix that will support single subqueries in on clauses. This will require fixing the offset in Blackboard.register, correctly detect if correlated variable is on the being used and adding a rule RelDecorrelator joined decorrelations.

Given:

SELECT *
FROM dept
LEFT JOIN bonus ON bonus.job = (
  SELECT emp.job
  FROM  emp
  WHERE  emp.deptno = dept.deptno
)

Should generate

LogicalProject(DEPTNO=[$0], NAME=[$1], ENAME=[$2], JOB=[$3], SAL=[$4], COMM=[$5])
  LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0}]) //requiring dept.DEPTNO as the correlated value
    LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
    LogicalFilter(condition=[=($1, $4)]) //This is comparing bonus.job = emp.job
      LogicalJoin(condition=[true], joinType=[left])
        LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
        LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
          LogicalProject(JOB=[$2])
            LogicalFilter(condition=[=($7, $cor0.DEPTNO)]) //all employees for a given department 
              LogicalTableScan(table=[[CATALOG, SALES, EMP]])

But instead generates:

LogicalProject(DEPTNO=[$0], NAME=[$1], ENAME=[$2], JOB=[$3], SAL=[$4], COMM=[$5])
  LogicalJoin(condition=[=($3, $0)], joinType=[left])
    LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
    LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0}])
      LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
      LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
        LogicalProject(JOB=[$2])
          LogicalFilter(condition=[=($7, $cor0.DEPTNO)])
            LogicalTableScan(table=[[CATALOG, SALES, EMP]])

Issue Links

• incorporates: CALCITE-1045 Decorrelate sub-queries in Project and Join
• links to: GitHub Pull Request #2217
• links to: GitHub Pull Request #2219
• links to: GitHub Pull Request #2364