Bug #3121 Subquery with IN() returns wrong result
Submitted: 9 Mar 2004 12:30 Modified: 9 Mar 2004 14:08
Reporter: Jeremy Cole (Basic Quality Contributor) (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1 OS:
Assigned to: Assigned Account CPU Architecture:Any

[9 Mar 2004 12:30] Jeremy Cole
Description:
Subquery returns NULL instead of 0/1 for an IN() when the column is INT auto_increment primary key.  Does not fail when the column is standard INT.

How to repeat:
mysql> create table t (i int not null auto_increment primary key);
Query OK, 0 rows affected (0.06 sec)

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

mysql> select 1 in (select i from t);
+------------------------+
| 1 in (select i from t) |
+------------------------+
|                   NULL |
+------------------------+
1 row in set (0.00 sec)
[9 Mar 2004 12:36] Dean Ellis
Verified against 4.1.2.

These return the expected results:

select 1 in (select concat(i,'') from t);
select 1 in (select i+0 from t);
[9 Mar 2004 14:08] Oleksandr Byelkin
Thank you for bugreport. 
 
This bug have same cause as BUG#2393, you can trace progress of this bug 
fixing using following url: 
http://bugs.mysql.com/bug.php?id=2393