| Bug #5367 | Subquery with "NOT IN" fails to return results in NDB tables | ||
|---|---|---|---|
| Submitted: | 2 Sep 2004 10:13 | Modified: | 7 Oct 2004 11:15 |
| Reporter: | Victoria Reznichenko | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S3 (Non-critical) |
| Version: | 4.1.4 | OS: | |
| Assigned to: | Jonas Oreland | CPU Architecture: | Any |
[9 Sep 2004 2:32]
ricardo oliveira
I've also noticed this bug before, after spending some HOURS trying to debug a piece of code. I'm using "NOT EXISTS" instead of "NOT IN". --Ricardo
[13 Sep 2004 6:26]
no name
SELECT id FROM `NewsletterList` WHERE id IN ( SELECT id FROM newsletterlist ) LIMIT 0 , 50 MySQL said: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id FROM newsletterlist ) LIMIT 0, 50' at line 5 but the following works... SELECT id FROM `NewsletterList` WHERE id IN ( 50, 51, 53 ) LIMIT 0 , 50 MySQL said: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id FROM newsletterlist ) LIMIT 0, 50' at line 5 Am I missing something here? thanks
[1 Oct 2004 13:31]
Martin Skold
I have asked Sanja to help with this one since we cannot see what the handler is doing wrong (does the same as for IN).
[5 Oct 2004 11:31]
Martin Skold
Changed the tables slightly to see what works and what does not: create table t1 (id int not null primary key, name char(20), xx int not null) engine=ndb; create table t2 (id int not null primary key, name char(20), id1 int not null key(id1)) engine=ndbcluster; insert into t1 values (1, 'aaa',1),(2,'bbb',2),(3,'ccc',3); insert into t2 values (1, 'aaa', 2),(2,'bbb',2),(3,'ccc',1), (4,'xxx',4), (5,'yyy',1); select * from t1; select * from t2; select * from t2 where id1 NOT IN (select xx from t1); works!! ---------------------------- create table t1 (id int not null primary key, name char(20),xx int not null, key (xx)) engine=ndb; create table t2 (id int not null primary key, name char(20), id1 int not null, key (id1)) engine=ndbcluster; insert into t1 values (1, 'aaa',1),(2,'bbb',2),(3,'ccc',3); insert into t2 values (1, 'aaa', 2),(2,'bbb',2),(3,'ccc',1), (4,'xxx',4), (5,'yyy',1); select * from t1; select * from t2; select * from t2 where id1 NOT IN (select xx from t1); Fails!! ------------------------------------ create table t1 (id int not null primary key, name char(20),xx int not null, unique (xx)) engine=ndb; create table t2 (id int not null primary key, name char(20), id1 int not null, key (id1)) engine=ndbcluster; insert into t1 values (1, 'aaa',1),(2,'bbb',2),(3,'ccc',3); insert into t2 values (1, 'aaa', 2),(2,'bbb',2),(3,'ccc',1), (4,'xxx',4), (5,'yyy',1); select * from t1; select * from t2; select * from t2 where id1 NOT IN (select xx from t1); Hangs!!
[5 Oct 2004 21:03]
Jonas Oreland
I have a patch which makes the query work.
1) Fix ignore error in TC during unique index read
2) Fix return codes on index_read
which should be different than on read_range
Will test rest of test cases tomorrow
[6 Oct 2004 11:36]
Tomas Ulin
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
[7 Oct 2004 11:15]
Oleksandr Byelkin
it is other fix of the problem: ChangeSet 1.2066 04/10/07 14:13:42 bell@sanja.is.com.ua +1 -0 correct (high level) fix for BUG#5367 to privent problems in other engines
[4 Aug 2011 17:23]
Moon Star
Can you help me? I have category, subcategory and product table. I want to show those products who have not sold using NOT IN()
Description: 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) 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) How to repeat: create table one (id int not null primary key, name char(20))engine=ndb; create table two (id int not null primary key, name char(20), id1 int not null, key (id1)) engine=ndb; insert into one values (1, 'aaa'),(2,'bbb'),(3,'ccc'); insert into two values (1, 'aaa', 2),(2,'bbb',2),(3,'ccc',1), (4,'xxx',4), (5,'yyy',1); select * from one; select * from two; select * from two where id1 IN (select id from one); select * from two where id1 NOT IN (select id from one);