Stamatis Zampetakis created CALCITE-4560:
--------------------------------------------
Summary: 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
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)
