[
https://issues.apache.org/jira/browse/CALCITE-4560?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17325218#comment-17325218
]
Stamatis Zampetakis commented on CALCITE-4560:
----------------------------------------------
In order to tackle the problem, I propose to add some transformations before
starting the core RelDecorrelator logic to bring the plan into an equivalent
but more convenient form that can be decorrelated into correct and more
efficient plans.
The transformations aim to bring the correlated predicate into the form
{{RexInputRef = RexFieldAccess(RexCorrelVariable)}} by extracting computations
over the input or correlation in {{Project}} operators.
+Plan before decorrelation+
{noformat}
LogicalProject(EMPNO=[$0])
LogicalFilter(condition=[IS NOT NULL($9)])
LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1}])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
LogicalAggregate(group=[{}], agg#0=[MIN($0)])
LogicalProject($f0=[true])
LogicalFilter(condition=[=(CASE(IS NOT NULL($cor0.ENAME),
$cor0.ENAME, 'M':VARCHAR(20)), CASE(IS NOT NULL($1), CAST($1):VARCHAR(20) NOT
NULL, 'M':VARCHAR(20)))])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
{noformat}
+Plan after new transformations(and other rules in the decorrelator)+
{noformat}
LogicalProject(EMPNO=[$0])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$9])
LogicalFilter(condition=[IS NOT NULL($9)])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$10])
LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{9}])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[CASE(IS NOT
NULL($1), $1, 'M':VARCHAR(20))])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
LogicalAggregate(group=[{}], agg#0=[MIN($0)])
LogicalProject($f0=[true])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalFilter(condition=[=($cor0.$f9, $9)])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[CASE(IS NOT
NULL($1), CAST($1):VARCHAR(20) NOT NULL, 'M':VARCHAR(20))])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
{noformat}
> Wrong plan when decorrelating EXISTS subquery with COALESCE in the predicate
> ----------------------------------------------------------------------------
>
> Key: CALCITE-4560
> URL: https://issues.apache.org/jira/browse/CALCITE-4560
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.27.0
> Reporter: Stamatis Zampetakis
> Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> The problem can be seen by adding the following test in
> {{SqlToRelConverterTest}}.
> {code:java}
> @Test void testExistsCorrelatedDecorrelate01() {
> final String sql = "select e1.empno from empnullables e1 where exists (\n"
> + " select 1 from empnullables e2 where
> COALESCE(e1.ename,'M')=COALESCE(e2.ename,'M'))";
> sql(sql).decorrelate(true).ok();
> }
> {code}
> The plan after decorrelation is shown below:
> {noformat}
> LogicalProject(EMPNO=[$0])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], ENAME0=[$9],
> $f1=[CAST($10):BOOLEAN])
> LogicalJoin(condition=[=($1, $9)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
> LogicalProject(ENAME0=[$9], $f0=[true])
> LogicalJoin(condition=[=(CASE(IS NOT NULL($9), $9,
> 'M':VARCHAR(20)), CASE(IS NOT NULL($1), CAST($1):VARCHAR(20) NOT NULL,
> 'M':VARCHAR(20)))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalAggregate(group=[{0}])
> LogicalProject(ENAME=[$1])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> {noformat}
> The problem lies in the {{LogicalJoin(condition=[=($1, $9)],
> joinType=[inner])}} operator. If there are rows with {{NULL}} values in the
> {{ENAME}} column these are going to be incorrectly removed from the result
> set. The COALESCE operator is present in the SQL query to ensure that rows
> with NULL values are retained in the result.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
