[Phpmyadmin-devel] subqueries

[Marc Delisle]

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