[DRILL-2356] Wrong result when ROUND function is used in expression

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 0.8.0
• Fix Version/s: 0.9.0
• Component/s: Functions - Drill
• Assignee: Mehant Baid
• Reporter: Victoria Markman
• Created: 02/Mar/15 18:28
• Updated: 07/May/15 21:32
• Resolved: 16/Apr/15 21:16

Description

Observe overflow in the expression SUM(ROUND ...)):

0: jdbc:drill:schema=dfs> select
. . . . . . . . . . . . >     sum(c_bigint)         as sum_c_bigint,
. . . . . . . . . . . . >     sum(ROUND(c_bigint/12))
. . . . . . . . . . . . > from
. . . . . . . . . . . . >     alltypes_with_nulls
. . . . . . . . . . . . > group by
. . . . . . . . . . . . >     c_varchar,
. . . . . . . . . . . . >     c_integer,
. . . . . . . . . . . . >     c_date,
. . . . . . . . . . . . >     c_time,
. . . . . . . . . . . . >     c_boolean;
+--------------+------------+
| sum_c_bigint |   EXPR$1   |
+--------------+------------+
| -3477884857818808320 | -2147483648 |
| 0            | 0          |
| 0            | 0          |
| 4465148082249531392 | 2147483647 |
| 4465148082249531392 | 2147483647 |
| -3999734748766273536 | -2147483648 |
| 0            | 0          |
| -449093763428515840 | -2147483648 |
| -1825551161692782592 | -2147483648 |
| -7308685202664980480 | -2147483648 |
| -6772904422084182016 | -2147483648 |
...
...

Wrapping ROUND around SUM, produces incorrect result as well:

0: jdbc:drill:schema=dfs> select
. . . . . . . . . . . . >     sum(c_bigint)         as sum_c_bigint,
. . . . . . . . . . . . >     ROUND(sum(c_bigint/12))
. . . . . . . . . . . . > from
. . . . . . . . . . . . >     alltypes_with_nulls
. . . . . . . . . . . . > group by
. . . . . . . . . . . . >     c_varchar,
. . . . . . . . . . . . >     c_integer,
. . . . . . . . . . . . >     c_date,
. . . . . . . . . . . . >     c_time,
. . . . . . . . . . . . >     c_boolean;
+--------------+------------+
| sum_c_bigint |   EXPR$1   | 
+--------------+------------+ 
| -3477884857818808320 | -2147483648 |
| 0            | 0          | 
| 0            | 0          | 
| 4465148082249531392 | 2147483647 |
| 4465148082249531392 | 2147483647 |
| -3999734748766273536 | -2147483648 |
| 0            | 0          |
| -449093763428515840 | -2147483648 |
| -1825551161692782592 | -2147483648 |
| -7308685202664980480 | -2147483648 |
| -6772904422084182016 | -2147483648 |
...
...

If you remove ROUND function, you get correct result:

0: jdbc:drill:schema=dfs> select
. . . . . . . . . . . . >     sum(c_bigint)         as sum_c_bigint,
. . . . . . . . . . . . >     sum(c_bigint/12)
. . . . . . . . . . . . > from
. . . . . . . . . . . . >     alltypes_with_nulls
. . . . . . . . . . . . > group by
. . . . . . . . . . . . >     c_varchar,
. . . . . . . . . . . . >     c_integer,
. . . . . . . . . . . . >     c_date,
. . . . . . . . . . . . >     c_time,
. . . . . . . . . . . . >     c_boolean;
+--------------+------------+
| sum_c_bigint |   EXPR$1   |
+--------------+------------+
| -3477884857818808320 | -289823738151567360 |
| 0            | 0          |
| 0            | 0          |
| 4465148082249531392 | 372095673520794282 |
| 4465148082249531392 | 372095673520794282 |
| -3999734748766273536 | -333311229063856128 |
| 0            | 0          |
...
...