Bug #101460 | Wrong result produced when left joining information_schema tables | ||
---|---|---|---|
Submitted: | 4 Nov 2020 13:25 | Modified: | 5 Mar 2021 16:31 |
Reporter: | Lukas Eder | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 8.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[4 Nov 2020 13:25]
Lukas Eder
[4 Nov 2020 17:02]
MySQL Verification Team
Hi Mr. Eder, Thank you for your bug report. W have run your test case, with some minor changes and we can conclude that, truly, the output is not correct. Verified as reported.
[4 Nov 2020 17:05]
MySQL Verification Team
Changing the category.
[5 Mar 2021 16:31]
Jon Stephens
Documented fix as follows in the MySQL 8.0.24 changelog: When interpreting the old-style plan to access paths, cache invalidators for LATERAL were delayed until all outer joins were completed, since outer joins could produce null-complemented rows that should also invalidate caches. Problems arose when an outer join had a LATERAL inside it, and that LATERAL referred only to tables from within the same outer join; in such cases the invalidator should be applied immediately and not delayed, lest we miss emitted rows, and the cache be incorrectly kept. In particular, this could happen when certain Information Schema tables were on the right side of an outer join, as these are now views defined using LATERAL. We fix this by delaying emission of the invalidator until we are inside the same (outer) join nest as the materialization to be invalidated, but no further. This also deals correctly with the case where rows from a table should invalidate two or more separate materializations, where some are within the join and some are higher up. Closed.
[9 Apr 2021 5:44]
Erlend Dahl
Bug#102949 values of TABLE_CONSTRAINTS are wrong when it is joined with STATISTICS was marked as a duplicate.
[1 Dec 2021 13:56]
Erlend Dahl
Bug#101276 Outer query LEFT JOIN uses only first match from inner query LEFT JOIN LATERAL was marked as a duplicate.
[1 Dec 2021 13:58]
MySQL Verification Team
Thank you, Erlend.