MySQL Bugs: #13419: NOT IN with one value returns 0 rows

Bug #13419	NOT IN with one value returns 0 rows
Submitted:	23 Sep 2005 5:08	Modified:	23 Sep 2005 23:40
Reporter:	Kolbe Kegel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.15	OS:	Linux (Linux)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
Selecting using NOT IN with only one predicate value against an indexed column returns 0 rows.

How to repeat:
create table t (id int, key(id));
insert into t values (1),(2),(3);
select count(*) from t where id not in (1);
select count(*) from t where id not in (1,2);

mysql 4.1.15-20050921-standard (root) "test"> select count(*) from t where id not in (1);
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql 4.1.15-20050921-standard (root) "test"> select count(*) from t where id not in (1,2);
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

Notes:

* NOT IN functions correctly if the column is not indexed
     alter table t drop index id;
     select count(*) from t where id not in (1);
     +----------+
     | count(*) |
     +----------+
     |        2 |
     +----------+

* Verified that myisam_stats_method has no effect
* Verified against MyISAM and InnoDB storage engines

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30257

Bug description:
 Queries that contain "t.key NOT IN(const)" could produce wrong results.

This bug was introduced by fix for BUG#12101 (which was pushed into 4.1.15) and the fix was pushed into 4.1.15 too, so this bug doesn't appear in released versions).

No changelog entry needed.