mysql-play/Examples/Join/bug_95411_test_case.sql at main · gdsotirov/mysql-play · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
/* A regression with LATERAL in MySQL 8.0.16
 * See https://bugs.mysql.com/bug.php?id=95411
 * See https://bugs.mysql.com/bug.php?id=94721
 */

/* 1. Create the sample DEPT/EMP database with the script from Examples/Schema/dept_emp.sql */

/* 2. Execute the following query: */

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;

/* 3. 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.

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.
*/

/* Fixed in MySQL 8.0.17 and above as the result is correct:

+------------+---------+-------------+---------+
| 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)

*/

/* 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)

*/