[CALCITE-4560] Wrong plan when decorrelating EXISTS subquery with COALESCE in the predicate

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.26.0
• Fix Version/s: 1.27.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Stamatis Zampetakis
• Reporter: Stamatis Zampetakis
• Created: 31/Mar/21 10:41
• Updated: 17/Apr/23 18:49
• Resolved: 29/Apr/21 13:43

Description

The problem can be seen by adding the following test in SqlToRelConverterTest.

  @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();
  }

The plan after decorrelation is shown below:

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

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.

Issue Links

• relates to: CALCITE-5390 RelDecorrelator throws NullPointerException
• relates to: HIVE-24957 Wrong results when subquery has COALESCE in correlation predicate
• links to: GitHub Pull Request #2406