Test Case

Setup

create table n1 (id STRING)
stored as parquet;

insert into n1 values ('-1234');
insert into n1 values ('7890');

Results

impalad version 2.12.0-cdh5.15.2

SELECT  `id`
,       CAST(`id` AS DECIMAL(38,0)) / 20 * 20 AS D0 
,       CAST(`id` AS DECIMAL(38,0)) / 20.0 * 20 AS D1
FROM    n1
ORDER BY `id`;

+----------+-------+---------+
| id       | d0    | d1      |
+----------+-------+---------+
| -1234    | -1220 | -1234.0 |
| 7890     | 7880  | 7890.0  |
+----------+-------+---------+
Fetched 4 row(s) in 0.56s

When we are casting the "id" column to a decimal without scale and dividing by an integer, even though the answer should have a scale (-1234 / 20 = -61.7) we lose the scale and the result is truncated to -61. This is then multiplied by 20 to give -1220 which is column d0. This does not get us back to the same value that we started with which was -1234.

When we divide by a decimal value (20.0) the result of -61.7 is preserved, which gets us back to the correct value of the "id" in column d1.

impalad version 3.2.0-cdh6.2.0

SELECT  `id`
,       CAST(`id` AS DECIMAL(38,0)) / 20 * 20 AS D0 
,       CAST(`id` AS DECIMAL(38,0)) / 20.0 * 20 AS D1
FROM    n1
ORDER BY `id`;

+-------+--------------+--------------+
| id    | d0           | d1           |
+-------+--------------+--------------+
| -1234 | -1234.000000 | -1234.000000 |
| 7890  | 7890.000000  | 7890.000000  |
+-------+--------------+--------------+

Notice the change in behaviour with this later version of Impala.

This looks to me like a bug that has been fixed.

Could you please confirm and point me to the IMPALA Jira that explains this bug?