| Bug #98547 | Strange SUM value with GROUP BY | ||
|---|---|---|---|
| Submitted: | 11 Feb 2020 7:18 | Modified: | 18 Feb 2020 0:15 |
| Reporter: | to vu | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.19 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Other (x64) | |
[11 Feb 2020 7:18]
to vu
[11 Feb 2020 7:18]
to vu
sample data
Attachment: test_sum.sql (application/octet-stream, text), 5.46 KiB.
[11 Feb 2020 13:41]
MySQL Verification Team
Hi Mr. vu, Thank you for your bug report. Would you please explain why do you thing that a value of SUM is strange, since you have used COALESCE() function and involved only floating point values. Thanks in advance.
[12 Feb 2020 0:25]
to vu
Hello Sinisa Milivojevic, It seems that the value randomly appear with SUM. When query runs, it returns different numbers each time. Best regards,
[12 Feb 2020 13:35]
MySQL Verification Team
Hi Mr. vu, I get the following results with 4 (four) different runs: plant_code material_group test test1 P551 C012 213504.36999999997 213504.36999999997 plant_code material_group test test1 P551 C012 213504.36999999997 213504.36999999997 plant_code material_group test test1 P551 C012 213504.36999999997 213504.36999999997 plant_code material_group test test1 P551 C012 213504.36999999997 213504.36999999997 Do you get any different results ???? If yes, can you send me your output ???
[13 Feb 2020 0:14]
to vu
Please try to re-run many time as possible. My results as following: P551 C012 213504.36999999997 213504.36999999997 P551 C012 213504.36999999997 425995.19 P551 C012 213504.36999999997 638486.0100000001 P551 C012 213504.36999999997 850976.8300000002 Best regards,
[13 Feb 2020 1:47]
to vu
I made another example with more data so you can easily simulate the issue: SELECT a.plant_code, a.material_group_nm, sum(COALESCE((a.over_61_day_qty) * (a.over_61_day_amount/a.over_61_day_qty),0)) test, sum((a.over_61_day_qty) * (a.over_61_day_amount/a.over_61_day_qty)) test1 FROM test_sum a where a.version='20200210' GROUP BY a.plant_code, a.material_group_nm;
[13 Feb 2020 1:47]
to vu
new example data
Attachment: test_sum_new.sql (application/octet-stream, text), 1.32 MiB.
[13 Feb 2020 13:57]
MySQL Verification Team
Hello Mr. vu, I have ran your new test case. I also changed a test case so that the query runs 2.000 (two thousand) times. Each of the 2.000 results are 100 % the same. I am setting the status to "Can't repeat".
[14 Feb 2020 0:51]
to vu
Our structure is Innodb Cluster (3 nodes). Did you try my latest example data?
[14 Feb 2020 0:56]
to vu
error single node Mysql (Windows 2012 x64)
Attachment: error-2.png (image/png, text), 49.48 KiB.
[14 Feb 2020 13:05]
MySQL Verification Team
Hi Mr. vu, Yes, we have tested with your latest data and ran the SELECT query 2.000 (two thousand) times, each run with a same result. Do you get this error only with InnoDB Cluster ????
[17 Feb 2020 0:16]
to vu
Dear Sinisa Milivojevic, I attached another picture which was tested on Windows x64 single node also as my last comment. Maybe my bug related with this: https://bugs.mysql.com/bug.php?id=97920 Best regards,
[17 Feb 2020 12:29]
MySQL Verification Team
Hi, Yes, I have seen your picture. However, even after running your latest test case 10 times, I can not repeat it. If you are testing it only on Windows, then this could be a duplicate of the bug that you mention. However, you claim that you see the error on CentOS.
[18 Feb 2020 0:15]
to vu
Hi, So what should I do for this situation? Waiting for next release? Best regards,
[18 Feb 2020 13:26]
MySQL Verification Team
Yes, that would be the best course of action if this bug is a duplicate of the one that is already fixed in 8.0.20. Nobody at this moment knows when will that release be out.
