[CALCITE-2066] RelOptUtil.splitJoinCondition() could not split condition with case after applying FilterReduceExpressionsRule

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.16.0
• Component/s: core
• Assignee: Julian Hyde
• Reporter: Vova Vysotskyi
• Created: 27/Nov/17 13:12
• Updated: 27/Feb/24 22:23
• Resolved: 11/Dec/17 23:33

Description

Query

select * from emp a emp b where coalesce(a.mgr, -1) = coalesce(b.mgr, -1)

has logical plan

LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9], ENAME0=[$10], JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15], DEPTNO0=[$16], SLACKER0=[$17])
  LogicalFilter(condition=[=(CASE(IS NOT NULL($3), $3, -1), CASE(IS NOT NULL($12), $12, -1))])
    LogicalJoin(condition=[true], joinType=[inner])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])

After applying FilterReduceExpressionsRule condition in filter is changed to

CASE(IS NOT NULL($12), CASE(IS NOT NULL($3), =($3, $12), =(-1, $12)), CASE(IS NOT NULL($3), =($3, -1), CAST(true):BOOLEAN))

When this condition is passed into RelOptUtil.splitJoinCondition() method, leftJoinKeys and rightJoinKeys lists are left empty and passed condition is returned.

Note: RelOptUtil.splitJoinCondition() works correctly for the initial condition without applying FilterReduceExpressionsRule.