Bug #103278 SUM(decimal) produces wrong result

Submitted: 12 Apr 2021 6:56 Modified: 12 Apr 2021 8:47
Reporter: xiaoyang chen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:8.0, 8.0.23, 5.7.33 OS:Any
Assigned to: CPU Architecture:Any

[12 Apr 2021 6:56] xiaoyang chen
Description:
Computing SUM(decimal) in derived table produces wrong result. 

How to repeat:
1. import data into table 

```
source /home/xychen/dump.sql; 

```

2. execute the query 

```
without derived table
 
mysql> select sum(C_DECIMAL) from ndptest_table9;
+-------------------------------------------------------------------------+
| sum(C_DECIMAL)                                                          |
+-------------------------------------------------------------------------+
| -204799999999999993548015078616886608561.307223454886617454292144834909 |
+-------------------------------------------------------------------------+

with derived table:

set optimizer_switch='derived_merge=off';

mysql> select * from (select sum(C_DECIMAL) from ndptest_table9) as dt;
+---------------------------------------------------------------------+
| sum(C_DECIMAL)                                                      |
+---------------------------------------------------------------------+
| -99999999999999999999999999999999999.999999999999999999999999999999 |
+---------------------------------------------------------------------+

``` 
Clearly, these two quries should have the same result. However, the result 
in the second query looks like 'overflow'.
[12 Apr 2021 7:33] xiaoyang chen
Sorry for that I cannot upload data file. Now I will re-describe this problem.

SQL for create table and insert data. 

```
CREATE TABLE `t2` (
  `C_KEY` int NOT NULL AUTO_INCREMENT,
  `C_DECIMAL` decimal(65,30) DEFAULT NULL,
  PRIMARY KEY (`C_KEY`)
)  ENGINE=InnoDB AUTO_INCREMENT=30001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT into t2(C_DECIMAL) values (913010113510121214.983760113510121214070750000000);
INSERT into t2(C_DECIMAL) values (913010113510121214.983760113510121214070750000000);
INSERT into t2(C_DECIMAL) values (913010113510121214.983760113510121214070750000000);
INSERT into t2(C_DECIMAL) values (913010113510121214.983760113510121214070750000000);
INSERT into t2(C_DECIMAL) values (913010113510121214.983760113510121214070750000000);
INSERT into t2(C_DECIMAL) values (1.000000000000000000000000000000);
INSERT into t2(C_DECIMAL) values (913010113510121214.983760113510121214070750000000);
INSERT into t2(C_DECIMAL) values (913010113510121214.983760113510121214070750000000);
INSERT into t2(C_DECIMAL) values (0.000000000000000000000000000000);
INSERT into t2(C_DECIMAL) values (913010113510121214.983760113510121214070750000000);
INSERT into t2(C_DECIMAL) values (1.000000000000000000000000000000);
INSERT into t2(C_DECIMAL) values (913010113510121214.983760113510121214070750000000);
INSERT into t2(C_DECIMAL) values (913010113510121214.983760113510121214070750000000);
INSERT into t2(C_DECIMAL) values (913010113510121214.983760113510121214070750000000);
INSERT into t2(C_DECIMAL) values (913010113510121214.983760113510121214070750000000);
INSERT into t2(C_DECIMAL) values (1.000000000000000000000000000000);
INSERT into t2(C_DECIMAL) values (0.000000000000000000000000000000);
INSERT into t2(C_DECIMAL) values (913010113510121214.983760113510121214070750000000);
INSERT into t2(C_DECIMAL) values (913010113510121214.983760113510121214070750000000);
INSERT into t2(C_DECIMAL) values (1.000000000000000000000000000000);
INSERT into t2(C_DECIMAL) values (913010113510121214.983760113510121214070750000000);
INSERT into t2(C_DECIMAL) values (913010113510121214.983760113510121214070750000000);
INSERT into t2(C_DECIMAL) values (913010113510121214.983760113510121214070750000000);
INSERT into t2(C_DECIMAL) values (0.000000000000000000000000000000);
INSERT into t2(C_DECIMAL) values (-99999999999999999999999999999999999.999999999999999999999999999999);
INSERT into t2(C_DECIMAL) values (913010113510121214.983760113510121214070750000000);
INSERT into t2(C_DECIMAL) values (-0.999999999999999999999999999999);
INSERT into t2(C_DECIMAL) values (913010113510121214.983760113510121214070750000000);
INSERT into t2(C_DECIMAL) values (-99999999999999999999999999999999999.999999999999999999999999999999); 
INSERT into t2(C_DECIMAL) values (1.000000000000000000000000000000);

```

Query result:

```
mysql> select sum(C_DECIMAL) from t2;
+----------------------------------------------------------------------+
| sum(C_DECIMAL)                                                       |
+----------------------------------------------------------------------+
| -199999999999999982652807843307696911.308557843307696932655749999997 |
+----------------------------------------------------------------------+

mysql> set optimizer_switch='derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from (select sum(C_DECIMAL) from t2) as dt;
+---------------------------------------------------------------------+
| sum(C_DECIMAL)                                                      |
+---------------------------------------------------------------------+
| -99999999999999999999999999999999999.999999999999999999999999999999 |
+---------------------------------------------------------------------+
```
[12 Apr 2021 8:47] MySQL Verification Team
Hello xiaoyang chen,

Thank you for the report and test case.
Verified as described. 

regards,
Umesh

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.