MySQL Bugs: #80802: "ORDER BY expression" does not allow alias to aggregate

Bug #80802	"ORDER BY expression" does not allow alias to aggregate
Submitted:	21 Mar 2016 9:22	Modified:	21 Mar 2016 10:50
Reporter:	Jiří Kavalík	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5..1/5.5/5.6/57	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When using alias to aggregated column in ORDER BY only plain alias is allowed, using it in any expression returns error.

http://sqlfiddle.com/#!9/e87bb/7

Workarounds:
- select the expression and use its alias
- use a derived table and order the outer one

How to repeat:
create table t(a int);
-- these work
select sum(a) x from t group by a order by x;
select sum(a) x from t group by a order by sum(a);
select sum(a) x from t group by a order by -sum(a);

-- this one wrongly gives "Reference 'x' not supported (reference to group function)"
select sum(a) x from t group by a order by -x;

Thank you for the bug report.

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.13 Source distribution PULL: 2016-MAR-05

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Database changed
mysql 5.7 > create table t(a int);
Query OK, 0 rows affected (0.04 sec)

mysql 5.7 > select sum(a) x from t group by a order by -x;
ERROR 1247 (42S22): Reference 'x' not supported (reference to group function)

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.31 Source distribution PULL: 2016-MAR-05

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > use test
Database changed
mysql 5.6 > create table t(a int);
Query OK, 0 rows affected (0.03 sec)

mysql 5.6 > select sum(a) x from t group by a order by -x;
ERROR 1247 (42S22): Reference 'x' not supported (reference to group function)
mysql 5.6 >

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.5.50 Source distribution PULL: 2016-MAR-05

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 > use test
Database changed
mysql 5.5 > create table t(a int);
Query OK, 0 rows affected (0.01 sec)

mysql 5.5 > select sum(a) x from t group by a order by -x;
ERROR 1247 (42S22): Reference 'x' not supported (reference to group function)

C:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --debug-info --prompt="mysql 5.1 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.74-Win X64 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 > use test
Database changed
mysql 5.1 > create table t(a int);
Query OK, 0 rows affected (0.01 sec)

mysql 5.1 > select sum(a) x from t group by a order by -x;
ERROR 1247 (42S22): Reference 'x' not supported (reference to group function)
mysql 5.1 >