[
https://issues.apache.org/jira/browse/CALCITE-4560?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17326121#comment-17326121
]
Haisheng Yuan commented on CALCITE-4560:
----------------------------------------
[~Chunwei Lei] Can you take a look? you might be familiar with that part.
> 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)
