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
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