MySQL Bugs: #99913: Group by corrupts data

Bug #99913	Group by corrupts data
Submitted:	17 Jun 2020 11:00	Modified:	19 Jun 2020 12:37
Reporter:	Craig Archer	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.20	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
In rare scenarios, nested left joins where join is not found causes data corruption when used 
with a group by on the primary key

The same join and group by worked fine in previous version (8.0.18)

How to repeat:
Using the database dump provided run the following query with no limits
(I appreciate there is no need for a group by in this query, but during debug and for problem simplification I have removed some join tables that produced multiple rows benefiting from the group by. The problem occurs if the extra tables are in or not)

SELECT t.id,t.cust_id,t.device_ref,d.status,dm.id AS model_id,dm.name AS model_name
FROM task t
LEFT JOIN device d ON t.device_ref = d.device_ref
LEFT JOIN device_model dm ON d.device_model_id = dm.id
GROUP BY t.id;

Note that for cust_id 003210 and 003211 the nulls from the outer join seem to be mixed between the two rows, when they should be on one. 

Add in
WHERE t.cust_id in ('003210', 003211);
and it is similarly corrupted

remove the group by and it works fine
add an ORDER BY t.cust_id afte r the GROUP BY and it seems to work ok
use GROUP BY t.cust_id instead and it seems to work ok

test database

Attachment: Dump20200617.sql (application/octet-stream, text), 580.51 KiB.

Hello Craig Archer,

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

regards,
Umesh

Actually just saw a case where GROUP BY t.cust_id didn't work, but I think wrapping the whole thing in another select does e.g. SELECT * FROM (SELECT ...) a;

Duplicate of 

Bug#99398 Data in wrong row on left join

which has been fixed in the upcoming 8.0.21 release.