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

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5669?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17715316#comment-17715316 ] 

Julian Hyde edited comment on CALCITE-5669 at 4/22/23 7:17 PM:
---------------------------------------------------------------

There's a potential problem with the Volcano planner. Suppose that the right-hand input of a {{Correlate}} is a {{RelSet}} with several {{RelNode}} instances, some of which use the variable and some of which do not. The fact that one {{RelNode}} does not use the variable proves that it is not necessary. But nevertheless, the other {{RelNode}} instances are now invalid, because they reference a variable that is not defined.

I believe that the right-hand {{RelNode}} will need to be moved into a new {{RelSet}} (with one fewer variable used) at some point between when constant reduction occurs and when it is put under the {{Join}}. It's not clear from the code that that is happening. Please check that it is.


was (Author: julianhyde):
There's a potential problem with the Volcano planner. Suppose that the right-hand input of a Correlate is a RelSet with several RelNode instances, some of which use the variable and some of which do not. The fact that one RelNode does not use the variable proves that it is not necessary. But nevertheless, the other RelNode instances are now invalid, because they reference a variable that is not defined.

I believe that the right-hand {{RelNode}} will need to be moved into a new {{RelSet}} (with one fewer variable used) at some point between when constant reduction occurs and when it is put under the Join. It's not clear from the code that that is happening. Please check that it is.

> 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
>            Priority: Major
>              Labels: pull-request-available
>
> Consider the following query correlated query.
> {code:sql}
> select * from emp as e where exists (select 1 from dept as d where e.empno = null)
> {code}
> The query basically returns an empty result because {{e.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)