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.
