Bug #13258 Server locked when execute query: SELECT ... IN (SELECT ...)

• Submitted: 16 Sep 2005 10:20
• Modified: 16 Sep 2005 15:21
• Reporter: Abel Pinto Muñoz
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.12
• OS: Windows (Windows)
• CPU Architecture: Any

[16 Sep 2005 10:20] Abel Pinto Muñoz

Description:
My program runs the query 
SELECT 1 FROM TESTGPF WHERE XPRECIO IN (SELECT XPRECIO FROM TESTGPF WHERE XPRECIO IN(0,10,100,1000,10000,1,11,101,1001,10001,2,12,102,1002,10002))

I expected MySQL to return 13 rows with value 1 and actually happened the server locked and sentence never finish.

How to repeat:
I create the table TESTGPF with these sentences:
CREATE TABLE TESTGPF (PK INTEGER NOT NULL, XPRECIO INTEGER, PRIMARY KEY(PK))
I insert 25000 rows:
INSERT INTO TESTGPF VALUES (1,2)
INSERT INTO TESTGPF VALUES (2,3)
INSERT INTO TESTGPF VALUES (3,4)
...
INSERT INTO TESTGPF VALUES (25000,25001)

And I runs then query
SELECT 1 FROM TESTGPF WHERE XPRECIO IN (SELECT XPRECIO FROM TESTGPF WHERE XPRECIO IN(0,10,100,1000,10000,1,11,101,1001,10001,2,12,102,1002,10002))

Then the server locked and sentence never finish.

However these sentences work correctly:
SELECT 1 FROM TESTGPF WHERE PK IN (0,10,100,1000,10000,1,11,101,1001,10001,2,12,102,1002,10002)
 
SELECT 1 FROM TESTGPF WHERE XPRECIO IN (0,10,100,1000,10000,1,11,101,1001,10001,2,12,102,1002,10002)

[16 Sep 2005 15:21] Valeriy Kravchuk

It does not hang forever, just for 25000 attempts to read all 25000 rows...

Look:

mysql> insert into testgpf (xprecio) select xprecio from testgpf;
Query OK, 32768 rows affected (3.40 sec)
Records: 32768  Duplicates: 0  Warnings: 0

mysql> update testgpf set xprecio = pk + 1;
Query OK, 65535 rows affected (4.45 sec)
Rows matched: 65536  Changed: 65535  Warnings: 0

mysql> select * from testgpf limit 10;
+----+---------+
| PK | XPRECIO |
+----+---------+
|  1 |       2 |
|  2 |       3 |
|  3 |       4 |
|  4 |       5 |
|  5 |       6 |
|  6 |       7 |
|  7 |       8 |
|  8 |       9 |
|  9 |      10 |
| 10 |      11 |
+----+---------+
10 rows in set (0.02 sec)

That is how I recreated your test case (slightly modified). Then:

mysql> explain SELECT 1 FROM TESTGPF WHERE XPRECIO IN (SELECT XPRECIO FROM TESTG
PF WHERE
    -> XPRECIO IN(0,10,100,1000,10000,1,11,101,1001,10001,2,12,102,1002,10002));

+----+--------------------+---------+------+---------------+------+---------+---
---+-------+-------------+
| id | select_type        | table   | type | possible_keys | key  | key_len | re
f  | rows  | Extra       |
+----+--------------------+---------+------+---------------+------+---------+---
---+-------+-------------+
|  1 | PRIMARY            | TESTGPF | ALL  | NULL          | NULL | NULL    | NULL | 66362 | Using where |
|  2 | DEPENDENT SUBQUERY | TESTGPF | ALL  | NULL          | NULL | NULL    | NULL | 66362 | Using where |
+----+--------------------+---------+------+---------------+------+---------+---
---+-------+-------------+
2 rows in set (0.10 sec)

So, for each of 66362 rows we have to perform a dependent subquery, that will, in turn, examine 66362 rows... Will work for very long time really.

mysql> delete from testgpf where pk > 10;
Query OK, 65526 rows affected (4.17 sec)

mysql> select count(*) from testgpf
    -> ;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.05 sec)

Now it works fast:

mysql> SELECT 1 FROM TESTGPF WHERE XPRECIO IN (SELECT XPRECIO FROM TESTGPF WHERE
    -> XPRECIO IN(0,10,100,1000,10000,1,11,101,1001,10001,2,12,102,1002,10002));

+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
+---+
3 rows in set (0.01 sec)

You other queries either use index (primary key) or does not demand this cycle. This is an example of how one can write a non-optimal SQL query...

[16 Sep 2005 16:17] Abel Pinto Muñoz

Thank you, very much.
I'm testing the new version of MySQL to evaluate it, and certainly some test are non-optimal SQL queries.