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: | |
| 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
[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.