Bug #119646 View + Type Conversion + IN Subquery Returns Wrong Result

• Submitted: 8 Jan 7:46
• Modified: 21 Jan 20:24
• Reporter: mu mu
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.33
• OS: Ubuntu (22.04)
• CPU Architecture: Any

[8 Jan 7:46] mu mu

Description:
When a view contains a type conversion (CAST) expression and is used in an IN subquery, MySQL returns wrong results (all rows duplicated) instead of returning only the matching rows.

How to repeat:
DROP TABLE IF EXISTS t_main, t_ref;
CREATE TABLE t_main (id INT, val INT);
CREATE TABLE t_ref (id VARCHAR(10), ref_id INT, val INT);
INSERT INTO t_main VALUES (1, 10), (2, 20), (3, 30);
INSERT INTO t_ref VALUES ('1', 1, 100), ('2', 2, 200), ('3', 3, 300);

CREATE OR REPLACE VIEW v_test AS
SELECT CAST(r.id AS UNSIGNED) AS casted_id
FROM t_ref r 
WHERE r.val > 150;

SELECT m.id, m.val
FROM t_main m
WHERE m.id IN (SELECT casted_id FROM v_test)
ORDER BY m.id;
--Result: `id=2, val=20; id=3, val=30` (2 rows)

mysql> SELECT m.id, m.val
    -> FROM t_main m
    -> WHERE m.id IN (SELECT casted_id FROM v_test)
    -> ORDER BY m.id;
+------+------+
| id   | val  |
+------+------+
|    1 |   10 |
|    1 |   10 |
|    2 |   20 |
|    2 |   20 |
|    3 |   30 |
|    3 |   30 |
+------+------+
6 rows in set (0.00 sec)
--Returns 6 rows (duplicates of all rows) instead of 2 rows

[8 Jan 10:30] Chaithra Marsur Gopala Reddy

Hi mu mu,

Thank you for the test case. Verified as described.

[21 Jan 20:24] Knut Anders Hatlen

Seems to be the same problem as bug#118512. Closing as duplicate.