[jira] [Created] (CALCITE-5669) Remove trivial correlates from the query plan

["Stamatis Zampetakis (Jira)" <ji...@apache.org>]

Stamatis Zampetakis created CALCITE-5669:
--------------------------------------------

             Summary: Remove trivial correlates from the query plan
                 Key: CALCITE-5669
                 URL: https://issues.apache.org/jira/browse/CALCITE-5669
             Project: Calcite
          Issue Type: Improvement
          Components: core
            Reporter: Stamatis Zampetakis
            Assignee: Stamatis Zampetakis


Consider the following query correlated query.
{code:sql}
select * from emp e where exists (select 1 from dept where empno=null)
{code}
The query basically returns an empty result cause {{empno=null}} is always false.

The plan for the query after applying the sub-query remove rule is shown below:
{noformat}
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
  LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
    LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      LogicalAggregate(group=[{0}])
        LogicalProject(i=[true])
          LogicalFilter(condition=[=($cor0.EMPNO, null)])
            LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{noformat}
After applying the reduce expressions rule the filter with the correlated condition will become false and the resulting plan would be the following.
{noformat}
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
  LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
    LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      LogicalAggregate(group=[{0}])
        LogicalProject(i=[true])
          LogicalValues(tuples=[[]])
{noformat}
Observe that now we have a {{LogicalCorrelate}} but there is no real correlation in the plan since the correlation variable on the right side disappeared. Depending on how rules are applied and which rules are used similar "trivial" correlates may appear.

The goal of this ticket is to provide the means to get rid of them.

One option would be to add a new rule (e.g., {{CorrelateToJoinRule}}) which detects that a correlate does not have correlations in the right side and turn the correlation to a join; then we could employ other existing rules (such as PruneEmptyRules) for joins and remove the newly created join altogether.

Another option, would be to introduce new pruning rule(s) for correlate (similar to those for joins) that will remove the correlate when its input is an empty values expression.



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