Bug #107569 WHERE clause didn’t work with INNER JOIN, WITH, and UNION ALL.
Submitted: 15 Jun 8:56 Modified: 15 Jun 9:16
Reporter: Issei Nakamoto Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.29 OS:Linux
Assigned to: CPU Architecture:x86
Tags: regression

[15 Jun 8:56] Issei Nakamoto
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)
```
[15 Jun 9:16] MySQL Verification Team
Hello Issei Nakamoto,

Thank you for the report and test case.
Verified as described,

regards,
Umesh