Bug #31 Bug with nested subselect IN (... IN ())

• Submitted: 15 Jan 2003 0:03
• Modified: 28 Jan 2003 10:41
• Reporter: Alexander Keremidarski
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1
• OS: Any (any)
• CPU Architecture: Any

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