Description:
left join + semijoin produce wrong results when using materialization strategy
How to repeat:
mysql> create table t(a int);
Query OK, 0 rows affected (0.66 sec)
mysql> insert into t values(1);
Query OK, 1 row affected (0.02 sec)
mysql> set optimizer_switch='firstmatch=off';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from (select 11) t1 left join (select a from t where a in (select a from t where a != 1)) t2 on true;
+----+------+
| 11 | a |
+----+------+
| 11 | 1 |
+----+------+
1 row in set (0.01 sec)
mysql> explain select * from (select 11) t1 left join (select a from t where a in (select a from t where a != 1)) t2 on true;
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+----------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+----------+------+----------+----------------+
| 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | PRIMARY | t | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | PRIMARY | <subquery4> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 5 | test.t.a | 1 | 100.00 | NULL |
| 4 | MATERIALIZED | t | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+----------+------+----------+----------------+
5 rows in set, 1 warning (0.00 sec)
mysql> set optimizer_switch='firstmatch=on';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from (select 11) t1 left join (select a from t where a in (select a from t where a != 1)) t2 on true;
+----+------+
| 11 | a |
+----+------+
| 11 | NULL |
+----+------+
1 row in set (0.00 sec)
mysql> explain select * from (select 11) t1 left join (select a from t where a in (select a from t where a != 1)) t2 on true;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------------------+
| 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | PRIMARY | t | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 1 | PRIMARY | t | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; FirstMatch(t) |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------------------+
4 rows in set, 1 warning (0.01 sec)