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:
None 
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:14] Daniel DeLorme
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'"
[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.