[DRILL-1018] small decimal(38,18) numbers compared equal to zero

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 0.4.0
• Component/s: Functions - Drill
• Assignee: Mehant Baid
• Reporter: Chun Chang
• Created: 18/Jun/14 21:31
• Updated: 31/Jul/14 06:31
• Resolved: 30/Jul/14 05:32

Description

small decimal(38,18) numbers such as 0.000000010000000000 or -0.000000000000000010 are all compared to equal to zero.

Postgres:
foodmart=# select c_row, cast(c_decimal38 as decimal(38,18)), mod(c_bigint, cast(c_decimal38 as decimal(38,18))) from data where c_row < 15 and cast(c_decimal38 as decimal(38,18)) <> 0;
c_row | c_decimal38 | mod
-----------------------------------------------------------------------------------
2 | 1.000000000000000000 | 0.000000000000000000
3 | 123456789.000000000000000000 | -1.000000000000000000
4 | 999999999.000000000000000000 | 12.000000000000000000
5 | -999999999.000000000000000000 | 123.000000000000000000
6 | 0.000000010000000000 | 0.000000000000000000
7 | -0.000000010000000000 | 0.000000000000000000
8 | 12345.678900000000000000 | 12243.822600000000000000
9 | 123456789.123456789000000000 | -36999908.918111010000000000
10 | 999999999999999999.000000000000000000 | 920320399049584.000000000000000000
11 | -999999999999999999.000000000000000000 | -922337203685477580.000000000000000000
12 | 0.000000000000000010 | 0.000000000000000000
13 | -0.000000000000000010 | 0.000000000000000000
14 | 12345678901234.123456789012340000 | 2147483647.000000000000000000
(13 rows)

Drill:
0: jdbc:drill:schema=dfs> select c_row, cast(c_decimal38 as decimal(38,18)), mod(c_bigint, cast(c_decimal38 as decimal(38,18))) from data where c_row < 15 and cast(c_decimal38 as decimal(38,18)) <> 0;
--------------------------------

c_row

EXPR$1

EXPR$2

--------------------------------

2	1.000000000000000000	0E-18
3	123456789.000000000000000000	1.000000000000000000
4	999999999.000000000000000000	12.000000000000000000
5	-999999999.000000000000000000	123.000000000000000000
8	12345.678900000000000000	12243.822600000000000000
9	123456789.123456789000000000	36999908.918111010000000000
10	999999999999999999.000000000000000000	920320399049584.000000000000000000
11	-999999999999999999.000000000000000000	922337203685477580.000000000000000000
14	12345678901234.123456789012340000	2147483647.000000000000000000

--------------------------------
9 rows selected (0.38 seconds)

Notice c_row 6, 7, 12 and 13 are missing for Drill due to the comparison error.