MySQL Bugs: #3800: Error Joining 2 Table with NOT IN

Bug #3800	Error Joining 2 Table with NOT IN
Submitted:	17 May 2004 16:17	Modified:	17 May 2004 18:14
Reporter:	Jean-Sébastien Goupil	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	mysql-4.1.1-alpha	OS:	Windows (WinXP Pro English)
Assigned to:	Timothy Smith	CPU Architecture:	Any

Description:
The SQL Command is supposed to return the number of lines available when executing this command.
But in the subquery, when the field1 is equal to 1, the numrows are equals to 0.
Here the query :
SELECT field1 FROM table1 WHERE field1 NOT IN (SELECT field1 FROM table2 WHERE field2='2')

That query is working because the subquery contains 3 in field1. Buf if we change field2='2' by field2='3', we get a subquery that contains 1 in field1.
In both case, it supposed to return the value 25 from the main query. But, in the second case, we get nothing.

How to repeat:
CREATE TABLE `table1` (
  `field1` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`field1`)
) TYPE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;

CREATE TABLE `table2` (
  `field1` int(11) NOT NULL default '0',
  `field2` int(11) NOT NULL default '0',
  PRIMARY KEY  (`field1`,`field2`)
) TYPE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `table1` VALUES (25);
INSERT INTO `table2` VALUES (1, 3);
INSERT INTO `table2` VALUES (3, 2);

$result = mysql_query("SELECT field1 FROM table1 WHERE field1 NOT IN (SELECT field1 FROM table2 WHERE field2='2')",$link);
$ligne = mysql_num_rows($result);
// Get 1

$result = mysql_query("SELECT field1 FROM table1 WHERE field1 NOT IN (SELECT field1 FROM table2 WHERE field2='3')",$link);
$ligne = mysql_num_rows($result);
// Get 0

If you remove the primary key from the table2. Both queries are working.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

I tested this under a recent build of the 4.1 source code, and the bug does not exist.  Both queries return 1 row.

Timothy