Description:
When using a CTE that contains both type conversion (e.g., CAST) and a WHERE condition, and then referencing that CTE in an IN or EXISTS subquery, MySQL incorrectly returns all rows from the outer query instead of only the rows that match the CTE's filtered results.
The CTE itself contains the correct filtered data when queried directly, but when used in a subquery, the optimizer incorrectly processes the type conversion and WHERE condition, causing all rows to be returned.
How to repeat:
mysql> DROP TABLE IF EXISTS t_cte1, t_cte2;
mysql> CREATE TABLE t_cte1 (id INT, val INT);
CREATE TABLE t_cte2 (id VARCHAR(20), val INT);
INSERT INTO t_cte1 VALUES (1, 10), (2, 20), (3, 30);
INSERT INTO t_cte2 VALUES ('1', 10), ('2', 25), ('4', 40);
mysql> SELECT id, val FROM t_cte1
-> WHERE id IN (SELECT CAST(id AS UNSIGNED) FROM t_cte2 WHERE val > 15)
-> ORDER BY id;
+------+------+
| id | val |
+------+------+
| 2 | 20 |
+------+------+
1 row in set (0.02 sec)
mysql> WITH cte AS (
-> SELECT CAST(id AS UNSIGNED) AS id FROM t_cte2 WHERE val > 15
-> )
-> SELECT id, val FROM t_cte1
-> WHERE id IN (SELECT id FROM cte)
-> ORDER BY id;
+------+------+
| id | val |
+------+------+
| 1 | 10 |
| 1 | 10 |
| 2 | 20 |
| 2 | 20 |
| 3 | 30 |
| 3 | 30 |
+------+------+
6 rows in set (0.00 sec)
mysql> WITH cte AS (
-> SELECT CAST(id AS UNSIGNED) AS id FROM t_cte2 WHERE val > 15
-> )
-> SELECT id, val FROM t_cte1 t1
-> WHERE EXISTS (SELECT 1 FROM cte WHERE cte.id = t1.id)
-> ORDER BY id;
+------+------+
| id | val |
+------+------+
| 1 | 10 |
| 1 | 10 |
| 2 | 20 |
| 2 | 20 |
| 3 | 30 |
| 3 | 30 |
+------+------+
6 rows in set (0.00 sec)
Description: When using a CTE that contains both type conversion (e.g., CAST) and a WHERE condition, and then referencing that CTE in an IN or EXISTS subquery, MySQL incorrectly returns all rows from the outer query instead of only the rows that match the CTE's filtered results. The CTE itself contains the correct filtered data when queried directly, but when used in a subquery, the optimizer incorrectly processes the type conversion and WHERE condition, causing all rows to be returned. How to repeat: mysql> DROP TABLE IF EXISTS t_cte1, t_cte2; mysql> CREATE TABLE t_cte1 (id INT, val INT); CREATE TABLE t_cte2 (id VARCHAR(20), val INT); INSERT INTO t_cte1 VALUES (1, 10), (2, 20), (3, 30); INSERT INTO t_cte2 VALUES ('1', 10), ('2', 25), ('4', 40); mysql> SELECT id, val FROM t_cte1 -> WHERE id IN (SELECT CAST(id AS UNSIGNED) FROM t_cte2 WHERE val > 15) -> ORDER BY id; +------+------+ | id | val | +------+------+ | 2 | 20 | +------+------+ 1 row in set (0.02 sec) mysql> WITH cte AS ( -> SELECT CAST(id AS UNSIGNED) AS id FROM t_cte2 WHERE val > 15 -> ) -> SELECT id, val FROM t_cte1 -> WHERE id IN (SELECT id FROM cte) -> ORDER BY id; +------+------+ | id | val | +------+------+ | 1 | 10 | | 1 | 10 | | 2 | 20 | | 2 | 20 | | 3 | 30 | | 3 | 30 | +------+------+ 6 rows in set (0.00 sec) mysql> WITH cte AS ( -> SELECT CAST(id AS UNSIGNED) AS id FROM t_cte2 WHERE val > 15 -> ) -> SELECT id, val FROM t_cte1 t1 -> WHERE EXISTS (SELECT 1 FROM cte WHERE cte.id = t1.id) -> ORDER BY id; +------+------+ | id | val | +------+------+ | 1 | 10 | | 1 | 10 | | 2 | 20 | | 2 | 20 | | 3 | 30 | | 3 | 30 | +------+------+ 6 rows in set (0.00 sec)