Bug #99398 Data in wrong row on left join
Submitted: 29 Apr 2020 14:21 Modified: 5 May 2020 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 2020 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 2020 14:22] Soner Sayakci 
Database Dump

Attachment: shopware.sql.gz (application/gzip, text), 619.84 KiB.
[29 Apr 2020 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 2020 11:32] MySQL Verification Team 
Hello Soner Sayakci,

Thank you for the report and test case.

regards,
Umesh
[5 May 2020 18:18] Trey Raymond 
considering this is an S1, and .21's release is still ~2 months off, why .22?
[6 May 2020 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 2020 4:39] Erlend Dahl 
Bug#99592 GROUP BY includes wrong data

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

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

was marked as a duplicate.