Igor Guzenko created CALCITE-2639: ------------------------------------- Summary: FilterReduceExpressionsRule causes ArithmeticException at execution time Key: CALCITE-2639 URL: https://issues.apache.org/jira/browse/CALCITE-2639 Project: Calcite Issue Type: Bug Affects Versions: 1.17.0 Reporter: Igor Guzenko Assignee: Julian Hyde
Consider logical plan generated for test case(put this in {{RelOptRuleTest}}): {code:java} @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); } {code} Before applying ReduceExpressionsRule.FILTER_INSTANCE rule, query plan is {code:java} 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]]) {code} Plan after applying the rule: {code:java} 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]]) {code} 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|https://github.com/apache/calcite/commit/b470a0cd4572c9f6c4c0e9b51926b97c5af58d3f]. -- This message was sent by Atlassian JIRA (v7.6.3#76005)