[
https://issues.apache.org/jira/browse/CALCITE-5669?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-5669:
---------------------------------
Description:
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.
was:
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.
> 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)
