You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "James Starr (Jira)" <ji...@apache.org> on 2022/12/02 18:22:00 UTC
[jira] [Updated] (CALCITE-5418) Nested Queries are not expanded properly

     [ https://issues.apache.org/jira/browse/CALCITE-5418?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

James Starr updated CALCITE-5418:
---------------------------------
    Description: 
SubQueryRemoveRule does not expand the subqueries correctly:
{code:sql}
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)){code}

 {code}
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]])
{code}

> Nested Queries are not expanded properly
> ----------------------------------------
>
>                 Key: CALCITE-5418
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5418
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: James Starr
>            Assignee: James Starr
>            Priority: Major
>
> SubQueryRemoveRule does not expand the subqueries correctly:
> {code:sql}
> 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)){code}
>  {code}
> 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]])
> {code}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)