[ https://issues.apache.org/jira/browse/CALCITE-3143?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17040646#comment-17040646 ]
Danny Chen commented on CALCITE-3143: ------------------------------------- Move forward to CALCITE-3142 and remove the fix version of this issue. > Dividing NULLIF clause may cause Division by zero error > ------------------------------------------------------- > > Key: CALCITE-3143 > URL: https://issues.apache.org/jira/browse/CALCITE-3143 > Project: Calcite > Issue Type: Bug > Components: core > Affects Versions: 1.16.0 > Reporter: Li Xian > Assignee: Feng Zhu > Priority: Critical > Labels: pull-request-available > > execution of query with pattern like below (column COL_A with type > BigDecimal) > {code:java} > select case when a < 77 and a > 0 then 99 else 88 end from > ( > select SUM(COL_A) / nullif(SUM(0),0) as a > from SOME_TABLE group by COL_B > ) > {code} > > will cause error java.lang.ArithmeticException: Division by zero. > And the generated code is like below. Division is executed on _input2 (which > is 0) > {code:java} > /* 154 */ public Object current() { > /* 155 */ final Object[] current = (Object[]) inputEnumerator.current(); > /* 156 */ final java.math.BigDecimal inp1_ = current[1] == null ? > (java.math.BigDecimal) null : > org.apache.calcite.runtime.SqlFunctions.toBigDecimal(current[1]); > /* 157 */ final int inp2_ = > org.apache.calcite.runtime.SqlFunctions.toInt(current[2]); > /* 158 */ final boolean v0 = inp2_ != 0; > /* 159 */ final java.math.BigDecimal v2 = > org.apache.calcite.runtime.SqlFunctions.divide(inp1_, new > java.math.BigDecimal( > /* 160 */ inp2_)); > /* 161 */ return new Object[] { > /* 162 */ inp1_ != null && v0 && > org.apache.calcite.runtime.SqlFunctions.lt(v2, > $L4J$C$new_java_math_BigDecimal_77_) && (inp1_ != null && v0 && > org.apache.calcite.runtime.SqlFunctions.gt(v2, > $L4J$C$new_java_math_BigDecimal_0_)) ? 99 : 88, > /* 163 */ current[2]}; > /* 164 */ } > {code} > > > And by tracing the code generation, I found that in > org.apache.calcite.adapter.enumerable.RexImpTable#implementNullSemantics > {code:java} > case FALSE: > // v0 != null && v1 != null && f(v0, v1) > for (Ord<RexNode> operand : Ord.zip(call.getOperands())) { > if (translator.isNullable(operand.e)) { > list.add( > translator.translate( > operand.e, NullAs.IS_NOT_NULL)); > translator = translator.setNullable(operand.e, false); > } > } > list.add(implementCall(translator, call, implementor, nullAs)); > return Expressions.foldAnd(list); > {code} > operand "SUM(COL_A) / nullif(SUM(0),0)" is set as nullable=false and caused > this error. > My understanding is that since operands are translated as NullAs.IS_NOT_NULL, > it's then safe to evaluate them as nullable=false. But in my case, the > evaluation with nullable=false will make "nullif(SUM(0), 0)" to be translated > to 0, and cause problem on the division. > After comment out the below line > {code:java} > translator = translator.setNullable(operand.e, false);{code} > the query will work. May I ask if it is ok to comment out that line? cause > that looks like solving my problem temporarily. > -- This message was sent by Atlassian Jira (v8.3.4#803005)