MySQL Bugs: #44117: ORDER BY does nor work for a GROUP BY alias

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:	None
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

Description:
Hello,

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. 

Louis

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

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?

Valeriy,

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

Sincerely,

Louis 

Below the table create command

CREATE TABLE `d` (
  `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'
) ENGINE=InnoDB DEFAULT CHARSET=latin1

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.

Valeriy,

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 :>

Louis