Bug #4132 MySQL hang up when run a subquery.

• Submitted: 14 Jun 2004 20:29
• Modified: 18 Jun 2004 3:55
• Reporter: Jun Ding
• Status: Can't repeat
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.0.a
• OS: Windows (WIndows XP)
• CPU Architecture: Any

[14 Jun 2004 20:29] Jun Ding

Description:
here's the statement I run:
select id from sr_sscr where id not in (select min(id) from sr_sscr group by source);
here's my sr_sscr table:
mysql> show create table sr_sscr;
+---------+----------------------------------------------------------------
| Table   | Create Table
+---------+----------------------------------------------------------------
| sr_sscr | CREATE TABLE `sr_sscr` (
  `id` int(11) default NULL,
  `foldid` int(11) default NULL,
  `name` varchar(60) NOT NULL default '',
  `source` text,
  `sourcetime` datetime NOT NULL default '0000-00-00 00:00:00',
  `lang` varchar(30) default NULL,
  UNIQUE KEY `pk_sr_sscr` (`id`),
  KEY `indx_sr_sscr_foldid` (`foldid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------+----------------------------------------------------------------
1 row in set (0.03 sec)

How to repeat:
have some data in this sr_sscr table. then run the SQL statement as above. mySQL just stoped there and do nothing.

Suggested fix:
at least give some error message if mySQL 5.0 doesn't suppory a subquery with group fubction in it.

[17 Jun 2004 9:57] Ramil Kalimullin

Hello!

Unfortunately I could not repeat the bug with some random data.
Please upload your data (minimal set).

Thank you.

[18 Jun 2004 3:55] MySQL Verification Team

Tested on XP:

mysql> select id from sr_sscr where id not in (select min(id) from sr_sscr group by
    -> source);
+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0.04 sec)

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.0-alpha-nt |
+----------------+
1 row in set (0.01 sec)

[18 Jun 2004 18:54] Jun Ding

I had the dump file with 3408 rows data inside. But it too big(about 1.7 M) to attach here and to post. Could you give me a email address I can email to you? Or you can go to http://2000e.biz/testdata/testdata.sql where I post this file to get it.

[18 Jun 2004 19:54] Jun Ding

by the way, here's the MySQL version I used:
mysql> select version();
+-----------------------+
| version()             |
+-----------------------+
| 5.0.0-alpha-max-debug |
+-----------------------+
1 row in set (0.01 sec)