Bug #3846 MySQL enters infinite loop for certain long query
Submitted: 21 May 2004 11:37 Modified: 21 May 2004 18:08
Reporter: Peter Deister Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:mysql Ver 11.18 Distrib 3.23.52, for su OS:Linux (Linux (SUSE 8.1))
Assigned to: CPU Architecture:Any

[21 May 2004 11:37] Peter Deister 
Description:
MySQL enters an infinite loop for certain long queries. The query consists of a from clause including various inner joins. Removing any item from the from-clause causes the problem to disappear.

How to repeat:
1. Setup a newly created database from the attached file "setup.sql".

2. Execute the query from the attached file "query.sql".

Suggested fix:
None
[21 May 2004 11:43] Peter Deister 
DDL and insert statements to set up the tables to be queried by query.sql

Attachment: setup.sql (application/octet-stream, text), 9.23 KiB.
[21 May 2004 11:44] Peter Deister 
the query causing the behaviour described in the bug report

Attachment: query.sql (application/octet-stream, text), 931 bytes.
[21 May 2004 17:14] Dean Ellis 
In at least 4.0.20 and 4.1.2 it is not an infinite loop but merely takes a very long time (10 minutes and 4 minutes respectively in my test) calculating statistics.  Verified; certainly room for improvement here.
[21 May 2004 18:08] Sergei Golubchik 
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

Additional info:

fixed in 5.0.1 with the introduction of a greedy search optimizer