Description:
Versions 5.7 and 8.0 return different results for a query depending on value of optimizer_switch='derived_merge=OFF';
How to repeat:
Run attached test case.
Output:
mysql> SELECT
-> Virtual_Table.T_FP AS T_FP,
-> (SELECT COUNT(Virtual_Table.T_FP) FROM t1 t WHERE t.f1 = Virtual_Table.T_FP AND Virtual_Table.T_FP = 731834939448428685) AS Test_Value
-> FROM
-> (SELECT t.f1 AS T_FP, tv.f1 AS TV_FP FROM t1 AS t JOIN t2 AS tv ON t.f1 = tv.t1_f1) AS Virtual_Table
-> GROUP BY Virtual_Table.TV_FP
-> HAVING Test_Value > 0;
Empty set (1.08 sec)
mysql> set optimizer_switch='derived_merge=OFF';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT Virtual_Table.T_FP AS T_FP, (SELECT COUNT(Virtual_Table.T_FP) FROM t1 t WHERE t.f1 = Virtual_Table.T_FP AND Virtual_Table.T_FP = 731834939448428685) AS Test_Value FROM (SELECT t.f1 AS T_FP, tv.f1 AS TV_FP FROM t1 AS t JOIN t2 AS tv ON t.f1 = tv.t1_f1) AS Virtual_Table GROUP BY Virtual_Table.TV_FP HAVING Test_Value > 0;
+--------------------+------------+
| T_FP | Test_Value |
+--------------------+------------+
| 731834939448428685 | 1 |
| 731834939448428685 | 1 |
| 731834939448428685 | 1 |
| 731834939448428685 | 1 |
| 731834939448428685 | 1 |
| 731834939448428685 | 1 |
| 731834939448428685 | 1 |
| 731834939448428685 | 1 |
| 731834939448428685 | 1 |
| 731834939448428685 | 1 |
| 731834939448428685 | 1 |
| 731834939448428685 | 1 |
| 731834939448428685 | 1 |
| 731834939448428685 | 1 |
| 731834939448428685 | 1 |
| 731834939448428685 | 1 |
| 731834939448428685 | 1 |
| 731834939448428685 | 1 |
| 731834939448428685 | 1 |
| 731834939448428685 | 1 |
| 731834939448428685 | 1 |
| 731834939448428685 | 1 |
+--------------------+------------+
22 rows in set (1.40 sec)
With smaller number of rows result is always correct.
Description: Versions 5.7 and 8.0 return different results for a query depending on value of optimizer_switch='derived_merge=OFF'; How to repeat: Run attached test case. Output: mysql> SELECT -> Virtual_Table.T_FP AS T_FP, -> (SELECT COUNT(Virtual_Table.T_FP) FROM t1 t WHERE t.f1 = Virtual_Table.T_FP AND Virtual_Table.T_FP = 731834939448428685) AS Test_Value -> FROM -> (SELECT t.f1 AS T_FP, tv.f1 AS TV_FP FROM t1 AS t JOIN t2 AS tv ON t.f1 = tv.t1_f1) AS Virtual_Table -> GROUP BY Virtual_Table.TV_FP -> HAVING Test_Value > 0; Empty set (1.08 sec) mysql> set optimizer_switch='derived_merge=OFF'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT Virtual_Table.T_FP AS T_FP, (SELECT COUNT(Virtual_Table.T_FP) FROM t1 t WHERE t.f1 = Virtual_Table.T_FP AND Virtual_Table.T_FP = 731834939448428685) AS Test_Value FROM (SELECT t.f1 AS T_FP, tv.f1 AS TV_FP FROM t1 AS t JOIN t2 AS tv ON t.f1 = tv.t1_f1) AS Virtual_Table GROUP BY Virtual_Table.TV_FP HAVING Test_Value > 0; +--------------------+------------+ | T_FP | Test_Value | +--------------------+------------+ | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | +--------------------+------------+ 22 rows in set (1.40 sec) With smaller number of rows result is always correct.