| 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: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.7 | OS: | Any (ALL) |
| Assigned to: | Jorge del Conde | CPU Architecture: | Any |
[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)
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.