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.