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/bugs?unsub=gcdmb-bugs@m.gmane.org