Bug #2393 NOT IN subquery cause main query returns no result
Submitted: 14 Jan 2004 20:00 Modified: 16 Mar 2004 5:09
Reporter: David Yao Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.1 OS:Windows (Windows XP)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[14 Jan 2004 20:00] David Yao 
Description:
When using subquery to restrict the main query result, if using in(.....) returns the right result, but when using NOT IN (....) if the subquery results more then a couple of rows, the main query returns no result.

How to repeat:
create table test_table 
(id int not null primary key, text varchar(20) not null default '');

insert into test_table (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text11'), (12, 'text12'); 

/*
the query below shoud return 7 rows, however, it returns nothing
*/

select * from test_table where id not in (select id from test_table where id < 8);
[15 Jan 2004 12:29] Dean Ellis 
Verified against 4.1.2/Linux.  Thank you.
[1 Feb 2004 16:43] Oleksandr Byelkin 
ChangeSet 
  1.1688 04/02/02 02:23:53 bell@sanja.is.com.ua +4 -0 
  avoid null check on fields where NULL is impossible 
  (BUG#2393)
[16 Mar 2004 5:09] Oleksandr Byelkin 
Thank you for bug report.  
 
This bug is fixed in our internal repository => fix will be present in next 
server release.
[9 May 2008 10:06] max williams 
I'm still seeing this bug in mysql version = 5.0.45-Debian_1ubuntu3.3-log
[10 May 2008 0:02] Sergey Petrunya 
Not repeatable on 5.0.51a on mysql-5.0.51a-linux-i686.tar.gz official binary.