[
https://issues.apache.org/jira/browse/CALCITE-2639?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16662116#comment-16662116
]
Vladimir Sitnikov commented on CALCITE-2639:
--------------------------------------------
[~IhorHuzenko], correlation does not imply causation, does it?
> 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
> Priority: Blocker
>
> 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)
