[
https://issues.apache.org/jira/browse/CALCITE-3143?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Li Xian updated CALCITE-3143:
-----------------------------
Description:
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 this
looks like solving my problem temporarily.
was:
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. 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 this
looks like solving my problem temporarily.
> 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
> Affects Versions: 1.16.0
> Reporter: Li Xian
> Priority: Critical
>
> 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
> this looks like solving my problem temporarily.
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
