Bug #44117 ORDER BY does nor work for a GROUP BY alias
Submitted: 6 Apr 2009 19:22 Modified: 7 Apr 2009 10:19
Reporter: Louis Breda van Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.1.33 OS:Any
Assigned to: CPU Architecture:Any
Tags: alias, GROUP BY, order by

[6 Apr 2009 19:22] Louis Breda van

Not sure if I should relate this bug to the parser or the optimiser, or what ever ...

I just noticed a report was not properly sorted.

Investigation learned that a sort based on a GROUP BY alias was not working.

See example below. 

No big deal to work arround, however quite unaccepable since querys should work as expected, otherwise no always noted errors will occur. 


How to repeat:
The select below is intended to fetch info related to a certain catogorie and order that info by NoOfOccurences desc.

However the sort does not work 

"SELECT d.importid, d.Cat, d.theinfo, sum(d.MyCount) As NoOccurences " + _
"FROM d " + _
"WHERE (d.Cat = '" + Cat + "') 
"GROUP BY d.importid, d.Cat, d.theinfo " + _
"ORDER BY NoOccurences desc;"

what does work is:

"SELECT d.importid, d.Cat, d.theinfo, sum(d.MyCount) As NoOccurences " + _
"FROM d " + _
"WHERE (d.Cat = '" + Cat + "') 
"GROUP BY d.importid, d.Cat, d.theinfo " + _
"ORDER BY sum(d.MyCount) desc;"

Suggested fix:
Just make it work as expected.

Note that IMHO that if MySQL can not proces a query for some reason, an error message is far more acceptable than unnoticed wrong behavoir
[7 Apr 2009 4:34] Valeriy Kravchuk
Thank you for the problem report. Please, send the results of:

show create table d\G

What exact version of MySQL server do you use?
[7 Apr 2009 6:20] Louis Breda van

Looking into the problem again I saw I made a slightly different mistake than described before my excuse (see below), but also that should have lead to an error message and not to a wrong query result

What happend
- alias name in my example is also a field name ^sum(d.MyCount) As MyCount^   
- I made a mistake in the query SELECT ....... SUM(d.MyCount) AS MyCount .... ORDER BY d.MyCount ==> that should have been ORDER BY MyCount (NOT!! d.MyCount)

I overlooked it!
I think with that mistake, the query is not valid!  
you can not sort on a not available field !?

Just for info, the table create statement below. MySQL release 5.1.33, vista64



Below the table create command

  `importid` int(10) NOT NULL DEFAULT '0',
  `abswk` int(10) DEFAULT NULL,
  `cat` varchar(60) DEFAULT NULL,
  `DetailKey` varchar(80) DEFAULT NULL,
  `theinfo` varchar(80) DEFAULT NULL,
  `MyCount` bigint(21) NOT NULL DEFAULT '0'
[7 Apr 2009 6:38] Valeriy Kravchuk
I expected something like this. So, you had ordered NOT by alias, but by column that you have in your d table. You got unexpected results, but this is not a bug. Read http://dev.mysql.com/doc/refman/5.1/en/select.html for some details.
[7 Apr 2009 10:19] Louis Breda van

I just looked at the web page you indicated, but do not understand to which part you are refering.

In general to be possible correct, it should be possible to explain the behavoir of a query.

Given the mistake I made, I really have not the slightest idea how the query should behave. It can not sort a column which is not part of the slection, and even if it could which value to take given the fact that it is a group by of records where each record probably have a different value for the non selected column.

So I am lost :>