Bug #2120 derived table query causes server to go 100 percent cpu
Submitted: 15 Dec 2003 13:14 Modified: 20 Feb 2004 5:55
Reporter: Edmund Mierzwa Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:4.1.1 alpha OS:Microsoft Windows (winxp)
Assigned to: Oleksandr Byelkin

[15 Dec 2003 13:14] Edmund Mierzwa
Description:
This derived table query causes server to go to 100 percent cpu. I then have to kill it using taskmgr. Funny thing is after I kill it, my client(mysqlcc 0.9.3 beta) displays the correct result set. If I run this query from the C API or "mysql" client it works just fine. This bug could of been reported against mysqlcc as well, but I believe mysqld should not lockup because of a client. Using mysqld-nt Alpha 4.1.1 on WinXP, Dell 2.6ghz 600mg ram.

Thanks,
Ed

How to repeat:
DROP TABLE IF EXISTS data;

CREATE TABLE data (
   name char(1) default NULL,
   val int(5) default NULL
) TYPE=MyISAM;

INSERT INTO data
VALUES ('a',1),  ('a',2),  ('a',2),  ('a',2),  ('a',3),  ('a',6),
        ('a',7),
        ('a',11), ('a',11), ('a',12), ('a',13), ('a',13), ('a',20),
        ('b',2), ('b',3), ('b',4), ('b',5);

SELECT s.name, AVG(s.val) AS median
FROM (SELECT x.name, x.val
      FROM data x, data y
      WHERE x.name=y.name
      GROUP BY x.name, x.val
      HAVING SUM(y.val <= x.val) >= COUNT(*)/2
         AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s
GROUP BY s.name

#Expected results, and the results that gets displayed after killing mysqld-nt.
+--------+--------+
| name   | median |
+--------+--------+
|      a | 7.0000 |
|      b | 3.5000 |
+--------+--------+

Suggested fix:
I can create a temporary table first, to avoid using derived tables or use a different client.
[15 Dec 2003 13:23] Dean Ellis
Verified against 4.1.2.  EXPLAIN on this query forces mysqld to restart (with nothing in the error log other than mentioning that mysqld restarted).

(The issue in MySQLCC is due to MySQLCC issuing an EXPLAIN against the query.)
[15 Dec 2003 16:42] Alexander Keremidarski
Further tests revealed that bug happens only when mysqld is compiled without debug options. Debug binaries return expected result from both query and EXPLAIN
[20 Dec 2003 2:06] Alexey Botchkov
bk commit - 4.1 tree (hf:1.1701) (20 Dec 2003)
[1 Feb 2004 5:36] Oleksandr Byelkin
ChangeSet 
  1.1683 04/02/01 15:30:32 bell@sanja.is.com.ua +18 -0 
  now all tables of query are locked in one place (including derived tables) 
  fixed BUG#2120 and other problem with EXPLAINing derived tables
[1 Feb 2004 10:13] Oleksandr Byelkin
as addition: 
ChangeSet 
  1.1689 04/02/01 20:07:44 bell@sanja.is.com.ua +6 -0 
  make JOIN::prepare, JOIN::optimize only once for EXPLAIN of derived table 
  (BUG#2120 sfter merge)
[20 Feb 2004 5:55] Oleksandr Byelkin
thank you for bug report. Putch for this bug is pushed to our internal source 
repository and will be present in next 4.1 release