Bug #105637 left join + semijoin produce wrong results when using materialization strategy
Submitted: 19 Nov 2021 9:52 Modified: 19 Nov 2021 10:18
Reporter: Xiaodi Z Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.25, 8.0.27 OS:Any
Assigned to: CPU Architecture:Any
Tags: left join, materialization, semijoin

[19 Nov 2021 9:52] Xiaodi Z
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)
[19 Nov 2021 10:18] MySQL Verification Team
Hello Xiaodi Z,

Thank you for the report and test case.

regards,
Umesh