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 Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.21, 5.7.31, 5.6.48 OS:Any
Assigned to: 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