Subquery with "NOT IN" fails to return results in NDB tables

A subquery with a "NOT IN" operator fails to return the right result 
when using NDB tables.

SELECT
     *
FROM
     table1
WHERE
     ID_column NOT IN
         (
         SELECT
             ID_column
         FROM
             table2
         )

Even though there are rows in table1 not matching the corresponding 
ID_column in table2, the query returns an empty set.

The same operation returns the right result when using MyISAM tables.

Test done using MySQL-max 4.1.4, binaries compiled by MySQL AB
(mysql-max-4.1.4-gamma-pc-linux-i686.tar.gz)

Cheers

gmax


--------------------------------------------------------
How to reproduce the bug:


mysql> create table one (id int not null primary key, name char(20)) 
engine=ndb;
mysql> create table two (id int not null primary key, name char(20), id1 
int not null, key (id1)) engine=ndb;

mysql> insert into one values (1, 'aaa'),(2,'bbb'),(3,'ccc');
mysql> insert into two values (1, 'aaa', 2),(2,'bbb',2),(3,'ccc',1), (4, 
'xxx',4), (5,'yyy',1);

mysql> select * from one;
+----+------+
| id | name |
+----+------+
|  2 | bbb  |
|  3 | ccc  |
|  1 | aaa  |
+----+------+

mysql> select * from two;
+----+------+-----+
| id | name | id1 |
+----+------+-----+
|  2 | bbb  |   2 |
|  4 | xxx  |   4 |
|  5 | yyy  |   1 |
|  3 | ccc  |   1 |
|  1 | aaa  |   2 |
+----+------+-----+
5 rows in set (0.00 sec)

mysql> select * from two where id1 IN (select id from one);
+----+------+-----+
| id | name | id1 |
+----+------+-----+
|  2 | bbb  |   2 |
|  5 | yyy  |   1 |
|  3 | ccc  |   1 |
|  1 | aaa  |   2 |
+----+------+-----+
4 rows in set (0.01 sec)

mysql> select * from two where id1 NOT IN (select id from one);
Empty set (0.01 sec)
# ----
# BUG. It should return row with id '4'
# ----

# =========================================================
# proof of concept. Let's repeat it with MyISAM tables

mysql> alter table one engine=myisam;
mysql> alter table two engine=myisam;

mysql> select * from two where id1 IN (select id from one);
+----+------+-----+
| id | name | id1 |
+----+------+-----+
|  2 | bbb  |   2 |
|  5 | yyy  |   1 |
|  3 | ccc  |   1 |
|  1 | aaa  |   2 |
+----+------+-----+
4 rows in set (0.00 sec)

mysql> select * from two where id1 NOT IN (select id from one);
+----+------+-----+
| id | name | id1 |
+----+------+-----+
|  4 | xxx  |   4 |
+----+------+-----+
1 row in set (0.00 sec)


-- 
Giuseppe Maxia
CTO
http://www.StarData.it
         __              __     __
    ___ / /____ ________/ /__ _/ /____ _
   (_-</ __/ _ `/ __/ _  / _ `/ __/ _ `/
/___/\__/\_,_/_/  \_,_/\_,_/\__/\_,_/
         Database is our business


-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]