[CALCITE-3143] Dividing by NULLIF may cause "ArithmeticException: Division by zero"

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Duplicate
• Affects Version/s: 1.16.0
• Fix Version/s: 1.24.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Feng Zhu
• Reporter: Li Xian
• Created: 24/Jun/19 11:25
• Updated: 28/May/21 10:12
• Resolved: 26/Jun/20 17:19

Description

execution of query with pattern like below (column COL_A with type BigDecimal) 

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
)

 

will cause error java.lang.ArithmeticException: Division by zero.

And the generated code is like below. Division is executed on _input2 (which is 0)

/* 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 */ }

 

 

And by tracing the code generation, I found that in org.apache.calcite.adapter.enumerable.RexImpTable#implementNullSemantics

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);

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

translator = translator.setNullable(operand.e, false);

the query will work. May I ask if it is ok to comment out that line? cause that looks like solving my problem temporarily. 

 

Issue Links

• duplicates: CALCITE-3224 New RexNode-to-Expression CodeGen Implementation
• relates to: CALCITE-3142 NullPointerException when rounding a nullable numeric
• relates to: CALCITE-3150 NullPointerException when UPPER is repeated and combined with LIKE
• relates to: CALCITE-3173 RexNode Code Generation Problem
• links to: GitHub Pull Request #1347