Description:
mysql> set optimizer_switch="derived_merge=off";
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT a.tinyint_col col1, a.varchar_col col2 FROM multi_tbl_3_all_type2index_desc a right join multi_tbl_3_all_type2index b on (a.tinyint_col,a.varchar_col) in (select tinyint_col col1, varchar_col col2 from view_tbl_1_all_type2index_desc a right join (SELECT tinyint_col col1, varchar_col col2 from multi_tbl_3_all_type2index where (multi_tbl_3_all_type2index.tinyint_col,multi_tbl_3_all_type2index.varchar_col) = (null, null)) b on a.smallint_col <> all(select smallint_col from multi_tbl_3_all_type2index order by 1) left join (SELECT 1 ORDER BY 1) c on false order by 1,2) group by 1,2;
+------+------+
| col1 | col2 |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)
mysql> set optimizer_switch="derived_merge=on";
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT a.tinyint_col col1, a.varchar_col col2 FROM multi_tbl_3_all_type2index_desc a right join multi_tbl_3_all_type2index b on (a.tinyint_col,a.varchar_col) in (select tinyint_col col1, varchar_col col2 from view_tbl_1_all_type2index_desc a right join (SELECT tinyint_col col1, varchar_col col2 from multi_tbl_3_all_type2index where (multi_tbl_3_all_type2index.tinyint_col,multi_tbl_3_all_type2index.varchar_col) = (null, null)) b on a.smallint_col <> all(select smallint_col from multi_tbl_3_all_type2index order by 1) left join (SELECT 1 ORDER BY 1) c on false order by 1,2) group by 1,2;
+------+-------------+
| col1 | col2 |
+------+-------------+
| 87 | bbbbbbbbbb |
| 74 | bbbbbbbbbb |
| 61 | bbbbbbbbbb |
| 126 | bbbbbbbbbb |
| 110 | bbbbbbbbcd |
| 108 | bbbbbbbbbb |
| 16 | bbbbbbbbbb |
| 98 | bbbbbbbbbb |
| 0 | ab |
| 127 | bbbbbbbbbaa |
| -128 | bbba2 |
| 42 | bbbbbbbbbb |
| 38 | bbbbbbbbbb |
| 25 | bbbbbbbbbb |
| 50 | bbbbbbbbbb |
+------+-------------+
15 rows in set (0.08 sec)
How to repeat:
source multi_tbl_3_all_type2index_desc.sql
source multi_tbl_3_all_type2index.sql
source tbl_1_all_type2index_desc.sql
create view view_tbl_1_all_type2index_desc as select * from tbl_1_all_type2index_desc;
set optimizer_switch="derived_merge=off";
SELECT a.tinyint_col col1, a.varchar_col col2 FROM multi_tbl_3_all_type2index_desc a right join multi_tbl_3_all_type2index b on (a.tinyint_col,a.varchar_col) in (select tinyint_col col1, varchar_col col2 from view_tbl_1_all_type2index_desc a right join (SELECT tinyint_col col1, varchar_col col2 from multi_tbl_3_all_type2index where (multi_tbl_3_all_type2index.tinyint_col,multi_tbl_3_all_type2index.varchar_col) = (null, null)) b on a.smallint_col <> all(select smallint_col from multi_tbl_3_all_type2index order by 1) left join (SELECT 1 ORDER BY 1) c on false order by 1,2) group by 1,2;
set optimizer_switch="derived_merge=on";
SELECT a.tinyint_col col1, a.varchar_col col2 FROM multi_tbl_3_all_type2index_desc a right join multi_tbl_3_all_type2index b on (a.tinyint_col,a.varchar_col) in (select tinyint_col col1, varchar_col col2 from view_tbl_1_all_type2index_desc a right join (SELECT tinyint_col col1, varchar_col col2 from multi_tbl_3_all_type2index where (multi_tbl_3_all_type2index.tinyint_col,multi_tbl_3_all_type2index.varchar_col) = (null, null)) b on a.smallint_col <> all(select smallint_col from multi_tbl_3_all_type2index order by 1) left join (SELECT 1 ORDER BY 1) c on false order by 1,2) group by 1,2;
