[CALCITE-5418] Nested Queries are not expanded properly

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: James Starr
• Created: 02/Dec/22 18:13
• Updated: 28/Feb/24 06:03

Description

SubQueryRemoveRule does not expand the subqueries correctly:

SELECT deptno
FROM emp e
WHERE EXISTS (
  SELECT *
  FROM dept d
  WHERE d.deptno = e.deptno
    AND exists(
      SELECT *
      FROM emp_address ea
      WHERE ea.empno = e.empno)
    AND exists(
      SELECT *
      FROM emp e2
      WHERE e2.deptno = d.deptno))

 

LogicalProject(DEPTNO=[$7])
  LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0, 7}])
    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
    LogicalAggregate(group=[{0}])
      LogicalProject(i=[true])
        LogicalFilter(condition=[=($0, $cor0.DEPTNO)])
          LogicalCorrelate(correlation=[$cor2], joinType=[inner], requiredColumns=[{0}])
            LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}]) <--- This is incorrect.
              LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
              LogicalAggregate(group=[{0}])
                LogicalProject(i=[true])
                  LogicalFilter(condition=[=($0, $cor0.EMPNO)])
                    LogicalTableScan(table=[[CATALOG, SALES, EMP_ADDRESS]])
            LogicalAggregate(group=[{0}])
              LogicalProject(i=[true])
                LogicalFilter(condition=[=($7, $cor2.DEPTNO)])
                  LogicalTableScan(table=[[CATALOG, SALES, EMP]])

Issue Links

• incorporates: CALCITE-6032 Multilevel correlated query is failing in RelDecorrelator code path
• is blocked by: CALCITE-5420 SqlToRel should populate the correlate id of a Project for queries with aggregates
• is blocked by: CALCITE-4792 SqlToRel should populate corralateId for join with corralated query in ON condition
• is duplicated by: CALCITE-5544 SCALAR_QUERY is nested within SCALAR_QUERY, and the inner SCALAR_QUERY is an associative subquery that references a variable of the external query, sql to rel will make mistakes
• is related to: CALCITE-5517 Nested (correlated) subqueries fail at validation | No SELECT scope for inner subquery
• is related to: CALCITE-5413 Nested Subqueries with correlated variables are not decorrelated correctly
• relates to: CALCITE-5421 SqlToRelConverter should populate correlateId for join with correlated query in HAVING condition
• links to: GitHub Pull Request #2996