Bug #4411 Server hangs when trying to SELECT MAX(id) from an empty HEAP table
Submitted: 5 Jul 2004 17:50 Modified: 12 Aug 2004 0:29
Reporter: Jeremy Jones Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.20 OS:Linux (Linux - Red Hat 9)
Assigned to: Antony Curtis CPU Architecture:Any

[5 Jul 2004 17:50] Jeremy Jones 
Description:
After creating a HEAP table with a primary key column called job_title_id, running 'SELECT MAX(job_title_id)' or 'EXPLAIN SELECT MAX(job_title_id)' causes the server to hang for a long time (i.e. indefinitely?).

How to repeat:
CREATE TABLE `job_titles` (
  `job_title_id` int(6) unsigned NOT NULL default '0',
  `job_title` char(18) NOT NULL default '',
  PRIMARY KEY  (`job_title_id`),
  UNIQUE KEY `job_title_id` (`job_title_id`,`job_title`)
) TYPE=HEAP;

SELECT MAX(job_title_id) FROM job_titles;

Suggested fix:
Have it realise the table is empty and not return any rows, as it does on myiasm tables.
[5 Jul 2004 17:51] Jeremy Jones 
By the way this seemed ok on version 3.23. I've just upgraded to 4.0.22 and that's when this started to happen.

Thanks.
[6 Jul 2004 22:02] Antony Curtis 
Code enters infinite loop in find_key_range() at opt_sum:395-404 
 
Following patch allows the loop to be exited: 
 
===== opt_sum.cc 1.30 vs edited ===== 
--- 1.30/sql/opt_sum.cc	2004-01-19 22:44:37 +00:00 
+++ edited/opt_sum.cc	2004-07-06 20:06:08 +01:00 
@@ -401,6 +401,7 @@ 
       /* Can't use this key, for looking up min() or max(), end if last one */ 
       if (key == 1) 
 	return 0; 
+      key &= ~1; 
     } 
     ref->key_length=0; 
     ref->key=idx;
[30 Jul 2004 15:54] Sergei Golubchik 
better to use

  key>>=1;

please, don't forget the test case
[30 Jul 2004 15:57] Sergei Golubchik 
oops, sorry. you'll also need idx++.
[12 Aug 2004 0:29] Antony Curtis 
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html