Bug #100985 Stored value for 0.001 is inconsistent, and casted to decimal is also different

• Submitted: 29 Sep 2020 7:42
• Modified: 29 Sep 2020 9:16
• Reporter: Yushan ZHANG
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.21, 5.7.31, 5.6.48
• OS: Any
• CPU Architecture: Any
• Tags: cast, decimal, double, FLOAT

[29 Sep 2020 7:42] Yushan ZHANG

Description:
mysql> select * from t1;
+-------+------+
| a     | b    |
+-------+------+
|     1 |   -1 |
| 0.001 |    0 |
|    -1 |    0 |
+-------+------+
3 rows in set (0.01 sec)

mysql> select sum(a) from t1;
+-----------------------+
| sum(a)                |
+-----------------------+
| 0.0009999999999998899 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select sum(a) as mysum from t1 group by b;
+--------+
| mysum  |
+--------+
|      1 |
| -0.999 |
+--------+
2 rows in set (0.00 sec)

mysql> select sum(mysum) from (select sum(a) as mysum from t1 group by b) as t;
+-----------------------+
| sum(mysum)            |
+-----------------------+
| 0.0010000000000000009 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select cast( (select sum(a) from t1) as decimal(30,30));
+--------------------------------------------------+
| cast( (select sum(a) from t1) as decimal(30,30)) |
+--------------------------------------------------+
|                 0.000999999999999889900000000000 |
+--------------------------------------------------+
1 row in set (0.02 sec)

                          
mysql> select cast( (select sum(mysum) from (select sum(a) as mysum from t1 group by b) as t) as decimal(30,30));
+----------------------------------------------------------------------------------------------------+
| cast( (select sum(mysum) from (select sum(a) as mysum from t1 group by b) as t) as decimal(30,30)) |
+----------------------------------------------------------------------------------------------------+
|                                                                   0.001000000000000000900000000000 |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

How to repeat:
create table t1 (a double, b int);
insert into t1 values (1,-1), (0.001,0), (-1, 0);

select sum(a) from t1;

select sum(a) as mysum from t1 group by b;
select sum(mysum) from (select sum(a) as mysum from t1 group by b) as t;

select cast( (select sum(a) from t1) as decimal(30,30));
                           
select cast( (select sum(mysum) from (select sum(a) as mysum from t1 group by b) as t) as decimal(30,30));

Suggested fix:
If 0.001 cannot be represented precisely, then it should have the same approximation stored value on the same machine.

[29 Sep 2020 9:16] MySQL Verification Team

Hello Yushan ZHANG,

Thank you for the report and test case.

regards,
Umesh