Bug #103278 SUM(decimal) produces wrong result

• Submitted: 12 Apr 2021 6:56
• Modified: 12 Apr 2021 8:47
• Reporter: xiaoyang chen
• Status: Verified
• Category: MySQL Server: DML
• Severity: S1 (Critical)
• Version: 8.0, 8.0.23, 5.7.33
• OS: Any
• 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