Description:
I’m Issei Nakamoto, a software engineer at iRidge, Inc (https://iridge.jp/).
WHERE clause didn’t work under the conditions below.
- MySQL 8.0.29
- Using a WITH clause
- Including UNION ALL inside the WITH clause
- Including INNER JOIN inside either table combined by UNION ALL
- Including WHERE outside the WITH clause
This problem seems to be caused by this optimization.
- https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-29.html#mysqld-8-0-29-optimizer
The reproduction code is the following.
How to repeat:
Code:
```
CREATE TABLE table1 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c1 INT,
KEY i1 (c1)
);
CREATE TABLE table2 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c1 INT,
KEY i1 (c1)
);
INSERT INTO table1 VALUES (1, 111);
INSERT INTO table1 VALUES (2, 222);
INSERT INTO table1 VALUES (3, 111);
WITH
t0 AS (
SELECT id
FROM table1
WHERE c1 IN (SELECT c1 FROM table1)
),
t1 AS (
SELECT id FROM table2
UNION ALL
SELECT id FROM table1 INNER JOIN t0 USING(id)
)
SELECT * FROM t1 WHERE id = 1;
```
Result:
```
+----+
| id |
+----+
| 1 |
| 3 |
+----+
```
EXPLAIN:
```
mysql> EXPLAIN WITH
-> t0 AS (
-> SELECT id
-> FROM table1
-> WHERE c1 IN (SELECT c1 FROM table1)
-> ),
-> t1 AS (
-> SELECT id FROM table2
-> UNION ALL
-> SELECT id FROM table1 INNER JOIN t0 USING(id)
-> )
-> SELECT * FROM t1 WHERE id = 1;
+----+-------------+------------+------------+--------+---------------+---------+---------+------------------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+------------------+------+----------+--------------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
| 3 | UNION | table1 | NULL | const | PRIMARY,i1 | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 3 | UNION | table1 | NULL | ref | PRIMARY,i1 | i1 | 5 | const | 2 | 100.00 | Using index |
| 3 | UNION | table1 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | idlink.table1.id | 1 | 100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------------------+------+----------+--------------------------------+
5 rows in set, 1 warning (0.01 sec)
```
On the other hand, I got the expected result by the following query.
Code:
```
WITH
t0 AS (
SELECT id
FROM table1
WHERE c1 IN (SELECT c1 FROM table1)
),
t1 AS (
SELECT id FROM table2 WHERE id = 1
UNION ALL
SELECT id FROM table1 INNER JOIN t0 USING(id) WHERE id = 1
)
SELECT * FROM t1;
```
Result:
```
+----+
| id |
+----+
| 1 |
+----+
```
EXPLAIN:
```
mysql> EXPLAIN WITH
-> t0 AS (
-> SELECT id
-> FROM table1
-> WHERE c1 IN (SELECT c1 FROM table1)
-> ),
-> t1 AS (
-> SELECT id FROM table2 WHERE id = 1
-> UNION ALL
-> SELECT id FROM table1 INNER JOIN t0 USING(id) WHERE id = 1
-> )
-> SELECT * FROM t1;
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+---------------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
| 3 | UNION | table1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
| 3 | UNION | table1 | NULL | const | PRIMARY,i1 | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 3 | UNION | table1 | NULL | ref | i1 | i1 | 5 | const | 2 | 100.00 | Using index; FirstMatch(table1) |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+---------------------------------+
5 rows in set, 1 warning (0.00 sec)
```
