[
https://issues.apache.org/jira/browse/CALCITE-2639?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16663130#comment-16663130
]
Julian Hyde commented on CALCITE-2639:
--------------------------------------
One idea (maybe for today, maybe for another time) is to rewrite AND to CASE.
The benefit of CASE is that we can carry predicates forward. Thus we rewrite
{code}mgr > 0 AND CASE WHEN mgr > 0 THEN deptno / mgr ELSE null END > 1{code}
to {code}CASE WHEN (mgr > 0) IS FALSE THEN FALSE ELSE CASE WHEN mgr > 0 THEN
deptno / mgr ELSE null END > 1{code} and then, when simplifying "WHEN mgr > 0",
we know that "mgr > 0" is not false. Given that ">" is strong, that means that
either mgr is null or mgr is positive.
> 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: Jesus Camacho Rodriguez
> 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)
