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