Bug #99398 Data in wrong row on left join
Submitted: 29 Apr 14:21 Modified: 5 May 17:02
Reporter: Soner Sayakci Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:8.0.20 OS:Linux (Docker)
Assigned to: CPU Architecture:x86
Tags: regression

[29 Apr 14:21] Soner Sayakci
Description:
Hey,
our nightly build failed today, cause an result from the database is not as expected.

In the SQL we fetch some categories and left join media (images). The media result is since 8.0.20 fixed with the correct row and one upper. Some information like width, height are still in the right row. It's very strange.

When I reduce the ids in the where the result looks good again. In previous MySQL 8 versions, this result was always right. 

How to repeat:
Database Dump in the attachments of the Issue
Previous Result, Current Result and the example SQL => https://gist.github.com/shyim/1d2e2f626b02da16743ae6f9fbba6690
[29 Apr 14:22] Soner Sayakci
Database Dump

Attachment: shopware.sql.gz (application/gzip, text), 619.84 KiB.

[29 Apr 14:25] Soner Sayakci
In the SQL we fetch some categories and left join media (images). The media result is since 8.0.20 mixed up with the correct row and one upper
[30 Apr 11:32] MySQL Verification Team
Hello Soner Sayakci,

Thank you for the report and test case.

regards,
Umesh
[5 May 18:18] Trey Raymond
considering this is an S1, and .21's release is still ~2 months off, why .22?
[6 May 13:59] Jon Stephens
Documented fix as follows in the MySQL 8.0.21 changelog:

    Refactoring work done in MySQL 8.0.20 caused single-row
    buffering for GROUP BY of non-nullable columns not to function
    correctly, not taking into account that such a column could be
    the inner table for an outer join, and thus would have a NULL
    flag that would need to be copied. In a GROUP BY without a
    temporary table, this would cause the NULL flag to come from the
    next output row instead of the previous one, and data returned
    to be inconsistent.

Closed.
[18 May 4:39] Erlend Dahl
Bug#99592 GROUP BY includes wrong data

was marked as a duplicate.
[18 May 4:46] Erlend Dahl
Bug#99587 Invalid data returned when using LEFT OUTER JOIN and GROUP BY

was marked as a duplicate.
[19 May 21:26] billy noah
Hello, this bug is currently wreaking havoc on my application.  Is there any viable workaround until 8.0.21 is released?
[19 Jun 12:38] Erlend Dahl
Bug#99913 Group by corrupts data

was marked as a duplicate.