Bug #31 Bug with nested subselect IN (... IN ())
Submitted: 15 Jan 2003 0:03 Modified: 28 Jan 2003 10:41
Reporter: Alexander Keremidarski Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1 OS:Any (any)
Assigned to:

[15 Jan 2003 0:03] Alexander Keremidarski
Description:
mysql> create table a (a int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table b (b int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into a values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into b values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select a from a where a in (select a from a where a in (select b from
b));
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

Should return only 1 row (=1)

... because
(select b from b);
+------+
| b    |
+------+
|    1 |
+------+
mysql> select a from a where a in (select b from b);
+------+
| a    |
+------+
|    1 |
+------+

How to repeat:
See above...