MySQL Bugs: #101460: Wrong result produced when left joining information

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:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I get wrong results when querying information_schema tables given the following pre-conditions:

- There are several foreign keys in a table pointing to a single table
- I'm using a left join between statistics and table_constraints

How to repeat:
Create this schema:

-- ------------------------------
create database x;
use x;
create table a (i int primary key);
create table b (
  i1 int, 
  i2 int,
  constraint fk1 foreign key (i1) references a (i),
  constraint fk2 foreign key (i2) references a (i)
);
-- ------------------------------

Verify the constraint meta data is there:

-- ------------------------------
select index_schema, table_name, index_name from information_schema.statistics where table_schema = 'x';
select constraint_schema, table_name, constraint_name from information_schema.table_constraints where table_schema = 'x';
-- ------------------------------

Output:

INDEX_SCHEMA|TABLE_NAME|INDEX_NAME|
------------|----------|----------|
x           |a         |PRIMARY   |
x           |b         |fk1       |
x           |b         |fk2       |

Now, try this query:

-- ------------------------------
select s.index_name, c.constraint_name
from information_schema.STATISTICS s
  left outer join information_schema.TABLE_CONSTRAINTS c
    on (
      s.INDEX_SCHEMA = c.CONSTRAINT_SCHEMA
      and s.TABLE_NAME = c.TABLE_NAME
      and s.INDEX_NAME = c.CONSTRAINT_NAME
    )
where s.TABLE_SCHEMA in ('x');
-- ------------------------------

It produces a wrong result:

INDEX_NAME|CONSTRAINT_NAME|
----------|---------------|
PRIMARY   |PRIMARY        |
fk1       |               |
fk2       |               |

Try this one, instead, with an inner join:

-- ------------------------------
select s.index_name, c.constraint_name
from information_schema.STATISTICS s
  join information_schema.TABLE_CONSTRAINTS c
    on (
      s.INDEX_SCHEMA = c.CONSTRAINT_SCHEMA
      and s.TABLE_NAME = c.TABLE_NAME
      and s.INDEX_NAME = c.CONSTRAINT_NAME
    )
where s.TABLE_SCHEMA in ('x');
-- ------------------------------

The result is now correct:

INDEX_NAME|CONSTRAINT_NAME|
----------|---------------|
PRIMARY   |PRIMARY        |
fk1       |fk1            |
fk2       |fk2            |

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.

Changing the category.

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.

Bug#102949 values of TABLE_CONSTRAINTS are wrong when it is joined with STATISTICS

was marked as a duplicate.

Bug#101276 Outer query LEFT JOIN uses only first match from inner query LEFT JOIN LATERAL

was marked as a duplicate.

Thank you, Erlend.