| Bug #30666 | Incorrect order when using range conditions on 2 tables or more | ||
|---|---|---|---|
| Submitted: | 28 Aug 2007 9:14 | Modified: | 19 Nov 2007 5:02 |
| Reporter: | Daniel DeLorme | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.0.22 - 5.0.45, 5.0 BK | OS: | Linux (Ubuntu 6.06) |
| Assigned to: | Alexey Kopytov | CPU Architecture: | Any |
[28 Aug 2007 9:18]
Valeriy Kravchuk
Thank you for a problem report. I do not see any data attached, though.
[28 Aug 2007 9:20]
Daniel DeLorme
Source data used to reproduce the bug
Attachment: bug-30666-source-data.sql (text/x-sql), 138.90 KiB.
[28 Aug 2007 19:35]
Sveta Smirnova
Thank you for the report. Verified as described using last development sources. Only version 5.0 is affected.
[29 Oct 2007 12:50]
Timour Katchaounov
Wrong order => wrong data, so P2.
[7 Nov 2007 11:01]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/37246 ChangeSet@1.2548, 2007-11-07 14:00:45+03:00, kaa@polly.(none) +3 -0 Fix for bug #30666: Incorrect order when using range conditions on 2 tables or more The problem was that the optimizer used the join buffer in cases when the result set is ordered by filesort. This resulted in the ORDER BY clause being ignored, and the records being returned in the order determined by the order of matching records in the last table in join. Fixed by relaxing the condition in make_join_readinfo() to take filesort-ordered result sets into account, not only index-ordered ones.
[16 Nov 2007 9:31]
Bugs System
Pushed into 5.0.52
[16 Nov 2007 9:33]
Bugs System
Pushed into 5.1.23-rc
[16 Nov 2007 9:36]
Bugs System
Pushed into 6.0.4-alpha
[19 Nov 2007 5:02]
Paul DuBois
Noted in 5.0.52, 5.1.23, 6.0.4 changelogs. The optimizer could ignore ORDER BY in cases when the result set is ordered by filesort, resulting in rows being returned in incorrect order.
[6 Mar 2008 6:45]
Sveta Smirnova
Bug #35061 was marked as duplicate of this one.
Description: It seems that if a query has range conditions on fields of 2 tables or more, the ORDER BY clause is ignored and the rows are returned in unsorted table order. I discovered the bug while developping on 5.0.22-Debian_0ubuntu6.06.3-log and I downloaded the binary distribution 5.0.45 and confirmed I still get the bug there. How to repeat: (using attached data) SELECT date_posted_utc FROM bug_articles, bug_texts, bug_locales WHERE bug_articles.id IN ( 17843, 17697, 17698, 17527, 17458, 17692, 17772, 17536, 17651, 17568 ) AND bug_locales.article_id = bug_articles.id AND bug_texts.article_id = bug_articles.id AND bug_locales.domain_locale = 'en-US' AND bug_texts.fieldname IN ('title', 'frontpage_intro') ORDER BY date_posted_utc DESC If I remove either the "bug_articles.id IN" condition or the "bug_texts.fieldname IN" conditions, I get the correct order. I also get the incorrect order if I use "bug_texts.fieldname >= 'frontpage_intro'"