MySQL Bugs: #119630: Optimizer incorrectly merges derived table with type conversion and WHERE clause, causing wrong query results

Bug #119630	Optimizer incorrectly merges derived table with type conversion and WHERE clause, causing wrong query results
Submitted:	6 Jan 7:59	Modified:	21 Jan 12:01
Reporter:	mu mu	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.33	OS:	Any (22.04)
Assigned to:		CPU Architecture:	Any

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）

Thank you for the bug report.
Verified as described.

This seems to be the same issue as bug#118512, fixed in MySQL 9.5.0. Closing as duplicate.

Right, fixed in 9.5, but still affecting 8.4.8 and 8.0.45...

...I added a comment about this in Bug#118512.

Thanks for opening this duplicate bug mu mu, I missed the other and the fact it was not fixed in all supported versions.