Bug #91878 Wrong results with optimizer_switch='derived_merge=ON';
Submitted: 2 Aug 23:09 Modified: 15 Aug 11:19
Reporter: Sveta Smirnova (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.23, 8.0.12, 5.7.22, 8.0.11 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[2 Aug 23:09] Sveta Smirnova
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.
[2 Aug 23:10] Sveta Smirnova
test case for MTR

Attachment: derived_merge.test (application/octet-stream, text), 763.27 KiB.

[3 Aug 5:08] Umesh Shastry
Hello Sveta,

Thank you for the report and test case.

regards,
Umesh
[15 Aug 11:19] Sveta Smirnova
Typo in the title.