MySQL Bugs: #27386: Queries are Returning Results Which are not Ordered

Bug #27386	Queries are Returning Results Which are not Ordered
Submitted:	22 Mar 2007 23:25	Modified:	9 Jul 2007 8:59
Reporter:	Bryan Anderson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.1.17 Beta	OS:	Any (Unknown(ISP) and Mac OS X)
Assigned to:		CPU Architecture:	Any
Tags:	GROUP BY, order by, SELECT

Description:
Please note,  the query below is an example.  The problem is happening on multiple queries of similar nature since upgrading from version 5.0.8 to 5.1.16 Beta.

In the specific query below, as an example,  I would expect that the drivers would be sorted in reverse order by the 'Total Points' value. 

SELECT d.driverID, d.DriverFirstName as 'First Name', d.DriverLastName as 'Last Name', SUM(stt.points_scored) AS 'Total Points'
					FROM drivers as d
					JOIN scoring_temp_table as stt ON d.driverID = stt.driverID
					JOIN schedule as sched ON stt.scheduleID = sched.scheduleID
					JOIN scoring_types as st ON stt.scoringID = st.scoringID
					JOIN series as ser ON sched.seriesID = ser.SeriesID 
					AND stt.scoringID IN (SELECT scoringID FROM pool_scoring WHERE poolID = 3) 
					AND st.scoringName LIKE '%LEAGUE%'
					AND sched.seriesID IN (SELECT seriesID FROM pool_scoring WHERE poolID = 3)
					GROUP BY d.driverID
					ORDER BY 'Total Points' DESC

What is happening on the output is a scattered order of 'Total Points'

Rank	First Name	Last Name	Total Points
1	KEV	HARV	482
2	MARK	MART	629
3	JEFF	BURT	618
4	MIKE	WALL	160
5	DAVID	RAG	386
6	ELLIOT	SAD	456
7	KASEY	KAH	259
8	DAVID	GILL	408
9	JOE	NEM	420
10	JEFF	GORD	621

It is important to note, that the results returned are valid from a total points perspective, but the order the results are displayed (or served to an application such as PHP) are not in the ORDER specified by the MySQL SELECT syntax.  I've reproduced the query output from MySQL commands line and is not an issue with PHP.

This same query and syntax has been used successfully used for quite a while now and have only encountered problems since the data was ported to 5.1.16.   

How to repeat:
It has been reproduced on my ISP as well as my local development environment when both were converted to 5.1.16.

Problem is reproducible every time.

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

You use sum() value in ORDER BY clause as in Bug #25376. So I'll mark this one as duplicate of that one.

Bug #25376 has been fixed in 5.1.17, so you have to wait when 5.1.17 will be released or download it from source repository. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Testing in Version 5.1.17 beta has shown the issue is not resolved and thus my issue was not a duplicate of #25376...

Please consider reopening and investigating my problem report.

Thank you for the feedback.

Please provide output of SHOW CREATE TABLE for every table used in the SELECT statement.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read about how identifiers should be quoted at http://dev.mysql.com/doc/refman/5.0/en/identifiers.html