[jira] [Comment Edited] (CALCITE-3717) Query fails with "division by zero" exception

["Feng Zhu (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3717?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17012636#comment-17012636 ] 

Feng Zhu edited comment on CALCITE-3717 at 1/10/20 9:45 AM:
------------------------------------------------------------

Yes, the issue is caused by code generation. However, it has two different reasons.

*Reason 1: Inline-Code Failed*

For the semantic below:
{code:java}
if (A) {null;} else {B}
{code}
Firstly, it generates code for A, and then unsafely generates code for B.

After that, Calcite tries best effort to make them as one-line code.
{code:java}
A (one-line)? null : B (one-line)
{code}
But unfortunately, it not always succeeds. The reason is same as that of CALCITE-3413.

 

*Reason 2: Wrong logic for Case-When*

Even the above problem is fixed, the query will still fails. For example:
{code:java}
CASE WHEN X THEN XX
     WHEN Y THEN YY
     ELSE ZZ
END{code}
The CASE operator is SQL’s way of handling if/then logic. The righ logic should be {color:#ff0000}nested if/then{color} as below:
{code:java}
xxx case_when_value;
......code for X......
if (!X_isNull && X_value) {
   ......code for XX......
   case_when_value = .....
} else {
   ......code for Y......
   if (!Y_isNull && Y_value) {
      ......code for YY......
      case_when_value = ...
   } else {
      ......code for ZZ......
      case_when_value = ...
   }
}
{code}
It's difficult to fix the problem in current implementation, but it can be naturally handled by my PR proposed in CALCITE-3224.

I'd like to add this test into it.


was (Author: donnyzone):
Yes, the issue is caused by code generation. However, it has two different reasons.

*Reason 1: Inline-Code Failed*

For the semantic below:

 
{code:java}
if (A) {null;} else {B}
{code}
Firstly, it generates code for A, and then unsafely generates code for B.

After that, Calcite tries best effort to make them as one-line code.

 
{code:java}
A (one-line)? null : B (one-line)
{code}
But unfortunately, it not always succeeds. The reason is same as that of CALCITE-3413.


 

*Reason 2: Wrong logic for Case-When*

Even the above problem is fixed, the query will still fails. For example:

{code:java}
CASE WHEN X THEN XX
     WHEN Y THEN YY
     ELSE ZZ
END{code}
The CASE operator is SQL’s way of handling if/then logic. The righ logic should be {color:#FF0000}nested if/then{color} as below:
{code:java}
xxx case_when_value;
......code for X......
if (!X_isNull && X_value) {
   ......code for XX......
   case_when_value = .....
} else {
   ......code for Y......
   if (!Y_isNull && Y_value) {
      ......code for YY......
      case_when_value = ...
   } else {
      ......code for ZZ......
      case_when_value = ...
   }
}
{code}
It's difficult to fix the problem in current implementation, but it can be naturally handled by my PR proposed in CALCITE-3224.

I'd like to add this test into it.

> Query fails with "division by zero" exception
> ---------------------------------------------
>
>                 Key: CALCITE-3717
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3717
>             Project: Calcite
>          Issue Type: Bug
>    Affects Versions: 1.21.0
>            Reporter: Hank Miller
>            Priority: Major
>             Fix For: 1.22.0
>
>
> Hi, community. We use Calcite-1.21 to connect and compute data resident in PostgreSQL and MySQL. It works pretty well. But we encountered a problem yesterday.
> Firstly, we combine data from different datasources.
> Assume the combined table is _T(...., c1, c2, c3)_, in which _c1, c2, c3_ are integer columns. Then according to our business logic, we need to perform further data processing in Calcite.
> {code:java}
> select case when c1=0 then ..
>             when c2=0 then 1.0/c1 + ...
>             when c3=0 then 1.0/c1 + 1.0/c2 + ...
>        else -99.99 end 
> from T{code}
> However, the query throws "java.lang.ArithmeticException: Division by zero".
> Anything goes wrong?



--
This message was sent by Atlassian Jira
(v8.3.4#803005)