Bug #95411 LATERAL produces wrong results (values insted of NULLs) on 8.0.16
Submitted: 17 May 17:39 Modified: 20 May 8:43
Reporter: Georgi Sotirov Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.16 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[17 May 17:39] Georgi Sotirov
Description:
The LATERL keyword, which makes it possible to use lateral derived tables (see https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html ), produces wrong result in MySQL 8.0.16, which seems to be a regression since 8.0.14, because in the same sample database the same query produces different results between the two versions (see details below).

How to repeat:
1. Run a new MySQL 8.0.14 container and a new MySQL 8.0.16 container from official images at DockerHub (see https://hub.docker.com/_/mysql ):

> docker run --name mysql8014 -e MYSQL_ROOT_PASSWORD=pass -d mysql:8.0.14
> docker run --name mysql8016 -e MYSQL_ROOT_PASSWORD=pass -d mysql:8.0.16

2. Open shell and start mysql client in both.

> docker exec -it mysql8014 bash
> docker exec -it mysql8016 bash
# mysql -u root -p # x2

3. In both create the sample DEPT/EMP database (e.g. see script at https://github.com/gdsotirov/mysql-play/blob/master/Examples/Schema/dept_emp.sql that is based on classic Oracle tables - see https://livesql.oracle.com/apex/livesql/file/content_O5AEB2HE08PYEPTGCFLZU9YCV.html ).
4. Execute the following query in both:

SELECT D.dname, LDT.min_sal, LDT.avg_sal, LDT.max_sal
  FROM dept D,
       LATERAL
       (SELECT MIN(E.sal) min_sal, AVG(E.sal) avg_sal, MAX(E.sal) max_sal
          FROM emp E
         WHERE E.deptno = D.deptno
       ) AS LDT;

5. The result in MySQL 8.0.14 is:

+------------+---------+-------------+---------+
| dname      | min_sal | avg_sal     | max_sal |
+------------+---------+-------------+---------+
| ACCOUNTING | 1300.00 | 2916.666667 | 5000.00 |
| RESEARCH   |  800.00 | 2175.000000 | 3000.00 |
| SALES      |  950.00 | 1566.666667 | 2850.00 |
| OPERATIONS |    NULL |        NULL |    NULL | <-- OK
+------------+---------+-------------+---------+
4 rows in set (0.00 sec)

Which is *correct*, because there are no employees registered for department OPERATIONS:

mysql> SELECT * FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE dname = 'OPERATIONS');
Empty set (0.00 sec)

6. The result in MySQL 8.0.16 however is:

+------------+---------+-------------+---------+
| dname      | min_sal | avg_sal     | max_sal |
+------------+---------+-------------+---------+
| ACCOUNTING | 1300.00 | 2916.666667 | 5000.00 |
| RESEARCH   |  800.00 | 2175.000000 | 3000.00 |
| SALES      |  950.00 | 1566.666667 | 2850.00 |
| OPERATIONS |  950.00 | 1566.666667 | 2850.00 | <-- !? KO
+------------+---------+-------------+---------+
4 rows in set (0.00 sec)

Which is *wrong*, because there are no employees registered for department OPERATIONS (see above). Seems like the last aggregates are copied on the next rows where NULLs should normally appear.

For comparison the following equivalent query with LEFT JOIN

SELECT D.dname, DT.min_sal, DT.avg_sal, DT.max_sal
  FROM dept D
       LEFT JOIN
       (SELECT E.deptno, MIN(E.sal) min_sal, AVG(E.sal) avg_sal, MAX(E.sal) max_sal
          FROM emp E
         GROUP BY E.deptno
       ) AS DT ON DT.deptno = D.deptno;

produces correct results in both MySQL 8.0.14 and 8.0.16 as follows:

+------------+---------+-------------+---------+
| dname      | min_sal | avg_sal     | max_sal |
+------------+---------+-------------+---------+
| ACCOUNTING | 1300.00 | 2916.666667 | 5000.00 |
| RESEARCH   |  800.00 | 2175.000000 | 3000.00 |
| SALES      |  950.00 | 1566.666667 | 2850.00 |
| OPERATIONS |    NULL |        NULL |    NULL |
+------------+---------+-------------+---------+
4 rows in set (0.00 sec)

Suggested fix:
LATERAL should again produce NULLs where no data is found by the dependent derived table query for the rows from preceding table.
[20 May 6:08] Umesh Shastry
Hello Georgi Sotirov,

Thank you for the report and feedback.
Verified as described.

Thanks,
Umesh
[20 May 8:43] Erlend Dahl
Duplicate of

Bug#94721 Wrong result with correlated LATERAL JOIN