Bug #25744 Nested Selects Crashing MySQL
Submitted: 22 Jan 2007 0:33 Modified: 12 May 2007 16:57
Reporter: Sean Gum Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.22 OS:Linux (CentOs 4.4)
Assigned to: CPU Architecture:Any
Tags: SELECT

[22 Jan 2007 0:33] Sean Gum 
Description:
We are attempting to migrate from MS Access over to MySQL. We are using MySql 5.0.22, but the problem also exists in previous versions.

This query crashes MySQLd. It maxes the CPU out at 100% for several minutes, then completely locks up the server.

SELECT * FROM posted_loads WHERE 1=1 AND pl_dcity in (SELECT lastlinecity FROM zip_codes WHERE lat BETWEEN '38.9914439942' AND '44.7801560058' AND lon BETWEEN '84.7285439942' AND '90.5172560058') AND pl_dstate in (SELECT state FROM zip_codes WHERE lat BETWEEN '38.9914439942' AND '44.7801560058' AND lon BETWEEN '84.7285439942' AND '90.5172560058') ORDER BY pl_weight ASC

The Query works just fine with MSAccess. I can't believe it actually crashes MySQL, but it does.

Please help!!!!
Thanks,
Sean Gum

How to repeat:
Simply execute that same query each time.

Suggested fix:
No workarounds found.
[22 Jan 2007 7:46] Valeriy Kravchuk 
Thank you for a problem report. Please, try to repeat with a newer version, 5.0.27. In case of the same problem, please, send the results of:

EXPLAIN SELECT * FROM posted_loads WHERE 1=1 AND pl_dcity in (SELECT lastlinecity FROM
zip_codes WHERE lat BETWEEN '38.9914439942' AND '44.7801560058' AND lon BETWEEN
'84.7285439942' AND '90.5172560058') AND pl_dstate in (SELECT state FROM
zip_codes WHERE lat BETWEEN '38.9914439942' AND '44.7801560058' AND lon BETWEEN
'84.7285439942' AND '90.5172560058') ORDER BY pl_weight ASC\G

and the results of SHOW CREATE TABLE and SHOW TABLE status for tables involved, posted_loads and zip_codes.
[23 Feb 2007 0:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[12 Apr 2007 16:57] Valeriy Kravchuk 
Feedback is still needed.
[12 May 2007 23:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".