Bug #98547 Strange SUM value with GROUP BY

Description:
Hello all,
I got strange SUM value as in my attachment sample. I wonder whether I make wrong calculation logic or not...
 - test column is correct
 - test1 is change randomly

Thanks & Best regards,

How to repeat:
SELECT 
a.plant_code,
a.material_group,
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'
and material_group='C012'
and plant_code='P551'
GROUP BY a.plant_code,
a.material_group

sample data

Attachment: test_sum.sql (application/octet-stream, text), 5.46 KiB.

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.

Hello Sinisa Milivojevic,
It seems that the value randomly appear with SUM.
When query runs, it returns different numbers each time.
Best regards,

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 ???

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,

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;

new example data

Attachment: test_sum_new.sql (application/octet-stream, text), 1.32 MiB.

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".

error image

Attachment: image.jpg (image/jpeg, text), 231.66 KiB.

Our structure is Innodb Cluster (3 nodes).
Did you try my latest example data?

error single node Mysql (Windows 2012 x64)

Attachment: error-2.png (image/png, text), 49.48 KiB.

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 ????

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,

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.

Hi,
So what should I do for this situation?
Waiting for next release?
Best regards,

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.