Why does this query simply *hang* MySQL 4.1

This works:

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?

MySQL 4.1.0-0 rpm install on stock Red Hat 7.2.

--
Jay Dolan
Software Engineer
Windmill Cycles, Inc.
Email: jdolan@windmillcycles.com
Tel: 1.800.CYCLE.22
Fax: 1.508.990.1042



--
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

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=3D'true');
>=20
> While this causes MySQL to simply hang:
>=20
> select id from table1 where table2_id in (select id from table2 where
> table3_id in (select id from table3 where select_me=3D'true'));
>=20
> In other words, MySQL just sits there when I enter a nested "in"
> subquery. No syntax complaints, no result set..just, nothin ;P
>=20
> 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 wher=
e
-> table3_id in (select id from table3 where select_me=3D'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

--=20
| Indrek Siitan, MySQL AB, Support Engineer & Bugmaster
| Uuem=F5isa, Haapsalu, Estonia
+-=20
| 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=3Dgcdmb-bugs@m.gmane.org

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

Hi,

> select id from table1 where table2_id in (select id from table2 where
> table3_id in (select id from table3 where select_me=3D'true'));
>=20
> In other words, MySQL just sits there when I enter a nested "in"
> subquery. No syntax complaints, no result set..just, nothin ;P

Thanks, I've got your test data now and it really did hang on 4.1.0.

However, IN has been greatly improved and optimized since and it will be
fixed in 4.1.1.


Rgds,
Indrek

--=20
| Indrek Siitan, MySQL AB, Support Engineer & Bugmaster
| Uuem=F5isa, Haapsalu, Estonia
+-=20
| 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=3Dgcdmb-bugs@m.gmane.org