Bug #66656 Referenced unallowed for no good reason from an IF in ORDER BY (error #1247)
Submitted: 2 Sep 2012 20:01 Modified: 5 Sep 2012 20:05
Reporter: matteo sisti sette Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0.77 OS:Linux
Assigned to: CPU Architecture:Any
Tags: error #1247, language, SQL

[2 Sep 2012 20:01] matteo sisti sette
Description:
If one can access a column by alias from within an ORDER BY condition, then there's no valid reason why one shouldn't be able to access the very same column by alias from an IF function within the very same ORDER BY condition.

See the example.

ADDITIONALLY (and it is equally important), the error message is very, very cryptic. Actually this is a general issue in MySQL: most error messages are close to useless. It is of critical importance that a more informative error message is issued (though in this case there should be no error in the first place)

How to repeat:
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 

Then why on earth shouldn't this:

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)
[5 Sep 2012 19:55] Sveta Smirnova
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.

Duplicate of bug #39455
[5 Sep 2012 20:05] matteo sisti sette
Sorry, I didn't find the other report when submitting this.