Bug #24935 ndbcluster subquery never exit
Submitted: 9 Dec 2006 12:55 Modified: 19 Jan 2007 21:43
Reporter: Andy Yeung Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:5.1.12 OS:Linux (Linux)
Assigned to: Assigned Account CPU Architecture:Any
Tags: ndbcluster, subquery

[9 Dec 2006 12:55] Andy Yeung
Description:
Subquery with more than 1 result return cause the whole query never exit and hang the client.

How to repeat:
mysql> select folderid from folder where userid='andy';
+-------------------------------+
| folderid                      |
+-------------------------------+
| f1770000000014571165652173169 | 
| f1770000000018091165653804080 | 
+-------------------------------+
2 rows in set (0.05 sec)

mysql> select fileid from photofile where folderid in (select min(folderid) from folder where userid='andy');
+-----------------------------------+
| fileid                            |
+-----------------------------------+
| p1770000000014551165653758207.jpg | 
| p1770000000016291165653629432.jpg | 
+-----------------------------------+
2 rows in set (0.11 sec)

mysql> select fileid from photofile where folderid in (select max(folderid) from folder where userid='andy');
+-----------------------------------+
| fileid                            |
+-----------------------------------+
| p1770000000017601165654739518.jpg | 
| p1770000000017611165654738282.jpg | 
| p1770000000010351165654738924.jpg | 
+-----------------------------------+
3 rows in set (0.05 sec)

mysql> select fileid from photofile where folderid in (select folderid from folder where userid='andy');

[THE QUERY NEVER END]
Query aborted by Ctrl+C
Aborted
[11 Dec 2006 15:16] Jonas Oreland
Hi,

Can you please upload DDL for folder and photofile, 
  to make sure that we get it right.

Also some data for easy reproduction wouldnt be bad...

/Jonas
[12 Dec 2006 9:35] Jonas Oreland
Hi,

I tried the following wo/ problem.

---

create table t1 (a int primary key, b int not null, index (b)) engine = ndb;
create table t2 (a int primary key, b int not null, index (b)) engine = ndb;

insert into t1 values (1,1), (2,2), (3,3), (4,4);
insert into t2 values (1,1), (2,2), (3,3), (4,4);

select * from t1 where a in (select a from t2 where a = 1);
select * from t1 where a in (select a from t2 where a > 1);
select * from t1 where b in (select a from t2 where a = 1);
select * from t1 where b in (select a from t2 where a > 1);

---

Conclusion: I need DDL/data

/Jonas
[14 Dec 2006 11:34] Andy Yeung
DB schema and data file which can trigger the problem

Attachment: photoalbums small.sql (text/plain), 40.46 KiB.

[14 Dec 2006 11:36] Andy Yeung
Hi, Sorry for the late reply. I have trimmed down the size of the data so that if you insert that into database and run this sql 

select title, description from photofile where folderid in (select folderid from folder where userid='photouser1');

In my machine, the mysql client will hang. I have tried randomly trim down of the data file but it didn't hang. But I can't find out the way to detect which data relationship cause this problem.
[19 Dec 2006 21:43] Jonas Oreland
Hi,

I tried back and forth to get any problem,
wo/ making it fail in any way....

Is the data correct?
There was a missing ";" in the file, maybe there is data missing in the file you uploaded...

/Jonas

ps. i only tried on 5.1.14 ds.
[20 Jan 2007 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".