Hi,
Working on bug 956744, something looks suspicious (at least to me) in MySQL 4.1.1:
CREATE TABLE `tab1` ( `id` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `tab1` VALUES (1); INSERT INTO `tab1` VALUES (2); INSERT INTO `tab1` VALUES (40);
CREATE TABLE `tab2` ( `id` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `tab2` VALUES (1); INSERT INTO `tab2` VALUES (2); INSERT INTO `tab2` VALUES (3); INSERT INTO `tab2` VALUES (4);
mysql> select id from tab1 where id in (select id from tab2); +----+ | id | +----+ | 1 | | 2 | +----+ 2 rows in set (0.00 sec) // ok
mysql> select id from tab1 where id not in (select id from tab2); Empty set (0.00 sec) // what?
mysql> select id from tab1 where id not in (1,2,3,4); +----+ | id | +----+ | 40 | +----+ 1 row in set (0.00 sec) // ok
OK, they do not mention NOT IN here: http://dev.mysql.com/doc/mysql/en/ANY_IN_SOME_subqueries.html
Someone got an explanation?
Marc