[CALCITE-2639] FilterReduceExpressionsRule causes ArithmeticException at execution time

• Type: Bug
• Status: Closed
• Priority: Blocker
• Resolution: Fixed
• Affects Version/s: 1.17.0
• Fix Version/s: 1.18.0
• Assignee: Jesús Camacho Rodríguez
• Reporter: Igor Guzenko
• Created: 24/Oct/18 09:39
• Updated: 27/Feb/24 22:23
• Resolved: 25/Oct/18 18:12

Description

Consider logical plan generated for test case(put this in RelOptRuleTest): 

 

@Test public void testOversimplifiedCaseStatement() {
    HepProgram program = new HepProgramBuilder()
        .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
        .build();

    String sql = "select * from emp " 
      + "where MGR > 0 and "
      + "case when MGR > 0 then deptno / MGR else null end > 1";
    checkPlanning(program, sql);
  }

Before applying  ReduceExpressionsRule.FILTER_INSTANCE rule, query plan is 

 

LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
  LogicalFilter(condition=[AND(>($3, 0), >(CASE(>($3, 0), /($7, $3), null), 1))])
    LogicalTableScan(table=[[CATALOG, SALES, EMP]])

Plan after applying the rule:

LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
  LogicalFilter(condition=[AND(>($3, 0), CASE(IS NOT NULL($3), >(/($7, $3), 1), false))])
    LogicalTableScan(table=[[CATALOG, SALES, EMP]])

Here LogicalFilter has condition AND(>($3, 0), CASE(IS NOT NULL($3), >(/($7, $3), 1), false)) where CASE condition was replaced by IS NOT NULL($3) condition. Since AND allows permutations for operands, the first operand may become CASE, so query may fail with ArithmeticException: / by zero error at execution stage.

The regression was caused by CALCITE-1413.

 

Issue Links

• is caused by: CALCITE-1413 New CASE statement simplification