MySQL Bugs: #35061: order by returns unsorted results with limit and force index

Bug #35061	order by returns unsorted results with limit and force index
Submitted:	5 Mar 2008 2:25	Modified:	6 Mar 2008 6:43
Reporter:	r b	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.50a	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	force index, limit, order by

Description:
The full repro is attached. On the MySQL version we are currently on (5.0.33-log), the query in the end returns unsorted results despite the ORDER BY clause.
The problem goes away if you do either of these:
-	Remove the LIMIT clause
-	Bump the LIMIT clause to a value >= 33
-	Remove the “force index” hints in the FROM clause.
The problem appears to be fixed on 5.1 but I would like to find a patch to 5.0.xx

This bug had similar symptoms http://bugs.mysql.com/bug.php?id=17212 but is fixed in our build.

Thanks

How to repeat:
repro is > 8k so will attach as a file

repro for this bug

Attachment: MySQLSortBugRepro.txt (text/plain), 8.30 KiB.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.51a, and inform about the results.

This does repro on the latest build

Thank you for the report.

I can not repeat described behavior with lates development sources, although bug is repeatable with current version 5.0.51a. Please wait next release.

Sveta, thank you for verifying that the bug reproes on the current 5.0 build and does not repro on 5.0.51.
It would be extremely helpful to know which checkin addressed this issue in 5.0.51.
We went through the list of fixes included in the various 5.0.x releases, but could not find any obvious candidate.
This is a blocking bug for us and waiting for the next release is unfortunately not an option.
We found a workaround, but unfortunately the workaround comes with a performance penalty.
But most of all we are concerned that if the root cause of the bug is not understood, we might run into the issue again with a different query.
This query pattern is fairly typical in our application, and if the bug in question was masked by some unrelated checkin - rather than fixed intentionally - we fear it could easily appear somewhere else down the road with a slightly different query.
I think that understanding exactly where the bug was and how it was fixed would be important, especially given how serious this issue is.
Not honoring an ORDER BY request is a wrong results bug.

This seems to be fix of bug #30666 which is available since version 5.0.52 (MySQL Enterprise Server)