MySQL Bugs: #39455: Would be useful to ORDER BY arithmetic expressions on GROUP BY columns

Bug #39455	Would be useful to ORDER BY arithmetic expressions on GROUP BY columns
Submitted:	15 Sep 2008 15:01	Modified:	4 Feb 2009 15:08
Reporter:	Chris Wilson	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S4 (Feature request)
Version:	5.0.51a-3ubuntu5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	order group by summary arithmetic expression

Description:
Consider the following table:

create table acct_v6(ip_src char(15), ip_dst char(15), bytes int, stamp_inserted datetime);

I can query the table for results grouped by total bytes downloaded:

select
sum(if(ip_src,bytes,0)) AS downloaded,
sum(if(ip_dst,bytes,0)) AS uploaded
FROM acct_v6 GROUP BY ip_src ORDER BY downloaded;

but not by total bytes download + uploaded:

select
sum(if(ip_src,bytes,0)) AS downloaded,
sum(if(ip_dst,bytes,0)) AS uploaded
FROM acct_v6 GROUP BY ip_src ORDER BY downloaded + uploaded;

ERROR 1247 (42S22): Reference 'downloaded' not supported (reference to group function)

How to repeat:
Try the queries above.

Suggested fix:
I guess this would require another stage of arithmetic evaluation and parsing after the GROUP BY phase.

Many thanks for writing a feature request.

This is related to; it is not possible to use function in order by.

Our development will discuss implementation of it.

Bug #66656 was marked as duplicate one.

Another example:

This works as expected:

SELECT username, COUNT( message.id ) AS n_messages
FROM user
LEFT JOIN message ON message.author_user_id = user.id GROUP BY user.id
ORDER BY n_messages DESC 

But this doesn't:

SELECT username, COUNT( message.id ) AS n_messages
FROM user
LEFT JOIN message ON message.author_user_id = user.id GROUP BY user.id
ORDER BY IF (n_messages>0, 0, 1), username DESC 

The second query results in the following error message:

#1247 - Reference 'n_messages' not supported (reference to group function)

Also note that the error message is very cryptic (but this is a general issue with error messages in MySQL)