Description:
When a derived table (subquery in FROM clause) contains type conversion (CAST) and a WHERE clause, and is used in an IN subquery, the optimizer's automatic merge strategy incorrectly processes the query, causing it to return all rows instead of only matching rows.
How to repeat:
-- Step 1: Create test tables
DROP TABLE IF EXISTS t_bug_mat1, t_bug_mat2;
CREATE TABLE t_bug_mat1 (id INT, val INT);
CREATE TABLE t_bug_mat2 (id VARCHAR(20), num_val INT);
INSERT INTO t_bug_mat1 VALUES (1, 10), (2, 20), (3, 30);
INSERT INTO t_bug_mat2 VALUES ('1', 10), ('2', 25), ('4', 40);
mysql> SELECT id, val FROM t_bug_mat1
-> WHERE id IN (SELECT CAST(id AS UNSIGNED) FROM t_bug_mat2 WHERE num_val > 15)
-> ORDER BY id;
+------+------+
| id | val |
+------+------+
| 2 | 20 |
+------+------+
1 row in set (0.01 sec)
mysql> SELECT t1.id, t1.val FROM t_bug_mat1 t1 WHERE t1.id IN ( SELECT /*+ NO_MERGE(subq) */ casted_id FROM ( SELECT CAST(id AS UNSIGNED) AS casted_id FROM t_bug_mat2 WHERE num_val > 15 ) AS subq ) ORDER BY t1.id;
+------+------+
| id | val |
+------+------+
| 2 | 20 |
+------+------+
1 row in set (0.01 sec)
mysql> SELECT t1.id, t1.val FROM t_bug_mat1 t1 WHERE t1.id IN ( SELECT casted_id FROM ( SELECT CAST(id AS UNSIGNED) AS casted_id FROM t_bug_mat2 WHERE num_val > 15 ) AS subq ) ORDER BY t1.id;
+------+------+
| id | val |
+------+------+
| 1 | 10 |
| 1 | 10 |
| 2 | 20 |
| 2 | 20 |
| 3 | 30 |
| 3 | 30 |
+------+------+
6 rows in set (0.00 sec) （wrong result）
Description: When a derived table (subquery in FROM clause) contains type conversion (CAST) and a WHERE clause, and is used in an IN subquery, the optimizer's automatic merge strategy incorrectly processes the query, causing it to return all rows instead of only matching rows. How to repeat: -- Step 1: Create test tables DROP TABLE IF EXISTS t_bug_mat1, t_bug_mat2; CREATE TABLE t_bug_mat1 (id INT, val INT); CREATE TABLE t_bug_mat2 (id VARCHAR(20), num_val INT); INSERT INTO t_bug_mat1 VALUES (1, 10), (2, 20), (3, 30); INSERT INTO t_bug_mat2 VALUES ('1', 10), ('2', 25), ('4', 40); mysql> SELECT id, val FROM t_bug_mat1 -> WHERE id IN (SELECT CAST(id AS UNSIGNED) FROM t_bug_mat2 WHERE num_val > 15) -> ORDER BY id; +------+------+ | id | val | +------+------+ | 2 | 20 | +------+------+ 1 row in set (0.01 sec) mysql> SELECT t1.id, t1.val FROM t_bug_mat1 t1 WHERE t1.id IN ( SELECT /*+ NO_MERGE(subq) */ casted_id FROM ( SELECT CAST(id AS UNSIGNED) AS casted_id FROM t_bug_mat2 WHERE num_val > 15 ) AS subq ) ORDER BY t1.id; +------+------+ | id | val | +------+------+ | 2 | 20 | +------+------+ 1 row in set (0.01 sec) mysql> SELECT t1.id, t1.val FROM t_bug_mat1 t1 WHERE t1.id IN ( SELECT casted_id FROM ( SELECT CAST(id AS UNSIGNED) AS casted_id FROM t_bug_mat2 WHERE num_val > 15 ) AS subq ) ORDER BY t1.id; +------+------+ | id | val | +------+------+ | 1 | 10 | | 1 | 10 | | 2 | 20 | | 2 | 20 | | 3 | 30 | | 3 | 30 | +------+------+ 6 rows in set (0.00 sec) （wrong result）