Bug #13994 "NOT IN" in query fails
Submitted: 13 Oct 2005 6:14 Modified: 13 Oct 2005 6:51
Reporter: Naveen Kumar Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.7 OS:Any (ALL)
Assigned to: Bugs System CPU Architecture:Any

[13 Oct 2005 6:14] Naveen Kumar
Description:

The "NOT IN" condition in where clause gives wrong result if the table has a primary key.  
If the table doesnt have a primary key, it properly gives the expected results.

For example :
****************
Execute this on any table which has a primary key......... It will give null set, though the expected result might be something else. 

SELECT * from tableName_HavingPrimaryKey where id not in ('1');
Empty set (0.02 sec)

In our case, this query was suppose to give 640 records.

And if we try the same query on any table which doesnt have any primary field.. it gives the expected result.

SELECT * from tableName_NoPrimaryKey where id not in ('1');
........
640 records in set(0.45 sec)

Work Around
****************

If the "NOT IN" clause is changed a bit.. as shown

SELECT * from tableName_HavingPrimaryKey where id not in ('1','1');
.................
640 records in set(0.45 sec)

ie, the same value entered twice in the "NOT IN" clause, it works properly.

How to repeat:

Any table which  has primary key will give the error.
[13 Oct 2005 6:51] Jorge del Conde
Hi!

I was unable to reproduce this bug in 5.0.13:

mysql> select User from user;
+----------+
| User     |
+----------+
| jorge    |
| public   |
| root     |
| user1    |
| user2    |
|          |
| root     |
|          |
| pippo1   |
| root     |
| testuser |
| jorge    |
+----------+
12 rows in set (0.00 sec)

mysql> select User from user where User not in ('jorge');
+----------+
| User     |
+----------+
| public   |
| root     |
| user1    |
| user2    |
|          |
| root     |
|          |
| pippo1   |
| root     |
| testuser |
+----------+
10 rows in set (0.00 sec)

mysql> select User from user where User not in ('jorge', 'root');
+----------+
| User     |
+----------+
| public   |
| user1    |
| user2    |
|          |
|          |
| pippo1   |
| testuser |
+----------+
7 rows in set (0.00 sec)