Re: Why does this query simply *hang* MySQL 4.1

Hi,

> select id from table1 where table2_id in (select id from table2 where
> select_me='true');
> 
> While this causes MySQL to simply hang:
> 
> select id from table1 where table2_id in (select id from table2 where
> table3_id in (select id from table3 where select_me='true'));
> 
> In other words, MySQL just sits there when I enter a nested "in"
> subquery.  No syntax complaints, no result set..just, nothin ;P
> 
> Is there a different syntax I should be using?

I just tried repeating the problem, but didn't succeed:

mysql> select version();
+----------------------+
| version()            |
+----------------------+
| 4.1.0-alpha-standard |
+----------------------+
1 row in set (0.02 sec)

mysql> create table table1 ( id int, table2_id int );
Query OK, 0 rows affected (0.20 sec)

mysql> create table table2 ( id int, table3_id int );
Query OK, 0 rows affected (0.01 sec)

mysql> create table table3 ( id int, select_me varchar(255) );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into table1 values (1,1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into table2 values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into table3 values (1,'true');
Query OK, 1 row affected (0.02 sec)

mysql> select id from table1 where table2_id in (select id from table2 where
    -> table3_id in (select id from table3 where select_me='true'));
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.03 sec)

Can you try the exact same thing, and if this works, provide us with the
table contents what make the query hang for you?


Rgds,
Indrek

-- 
|  Indrek Siitan, MySQL AB, Support Engineer & Bugmaster
|  Uuemõisa, Haapsalu, Estonia
+- 
|  Are you MySQL certified? http://www.mysql.com/certification/
--


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/bugs?unsub=gcdmb-bugs@xxxxxxxxxxx