[ 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)