Bug #109252 Incorrect result with VALUES in a correlated LATERAL subquery
Submitted: 30 Nov 2022 17:26 Modified: 3 Jan 8:03
Reporter: Sébastien F. Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.31 OS:Ubuntu
Assigned to: CPU Architecture:x86

[30 Nov 2022 17:26] Sébastien F.
Description:
VALUES statement in a correlated LATERAL subquery gives 1 final row.

How to repeat:
Request :

with dataset (name) as (values row ('Marc'), row ('Gabrielle'), row ('Antoine'))
select all *
from dataset
cross join lateral (values row (dataset.name)) as dl;

Gives :

name      column_0
----      --------
Marc      Marc

Awaited result :

name      column_0
----      --------
Marc      Marc
Gabrielle Gabrielle
Antoine   Antoine

Another DBRM like PgSQL gives the awaited result.

Thx!
[1 Dec 2022 6:28] MySQL Verification Team
Hello Sébastien F.,

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

regards,
Umesh
[3 Jan 8:03] Jon Stephens
Documented fix as follows in the MySQL 8.3.0 changelog:

    A VALUES statement in a correlated lateral subquery yielded an
    incorrect result.

Closed.
[17 Jan 18:30] Jean-François Gagné
This is flagged as fixed in 8.3.0, but also as affecting 8.0.31.

Will this be fixed in a future 8.0 release.
[27 Feb 17:14] Jon Stephens
Also fixed in MySQL 8.0.37 and 8.4.0.

Changelog entry as above.