Bug #188 Alias name matching column name
Submitted: 24 Mar 2003 23:57 Modified: 25 Mar 2003 2:32
Reporter: Peter Zaitsev (Basic Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.13 OS:Any (All)
Assigned to: CPU Architecture:Any

[24 Mar 2003 23:57] Peter Zaitsev
Description:
There seems to be uneven query execution if one uses function alias named the same as column from the table.

In case we use abs(alias) sort is done by  original column  while if it is "-alias"   sort is done by alias:

At least it should be uniform. 

How to repeat:
mysql> create table mytable(ccy int, myval int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into mytable values (1,2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mytable values (2,2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mytable values (3,2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mytable values (3,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mytable values (4,1);
Query OK, 1 row affected (0.00 sec)

mysql> select ccy, myval myval_old, sum(myval) as myval from mytable group  by ccy order by abs(myval) desc;
+------+-----------+-------+
| ccy  | myval_old | myval |
+------+-----------+-------+
|    1 |         2 |     2 |
|    2 |         2 |     2 |
|    3 |         2 |     3 |
|    4 |         1 |     1 |
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> select ccy, myval myval_old, sum(myval) as myval from mytable group  by ccy order by -myval desc;
+------+-----------+-------+
| ccy  | myval_old | myval |
+------+-----------+-------+
|    4 |         1 |     1 |
|    1 |         2 |     2 |
|    2 |         2 |     2 |
|    3 |         2 |     3 |
+------+-----------+-------+
4 rows in set (0.00 sec)

Also it is strange first variant is allowed while it is not for different alias name:

mysql> select ccy, myval myval_old, sum(myval) as myval_new from mytable group  by ccy order by abs(myval_new) desc;
ERROR 1054: Unknown column 'myval_new' in 'order clause'

Suggested fix:
[25 Mar 2003 2:32] Michael Widenius
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

If you are using an alias that is the same name as an existing column the behaviour is not defined.

Anyway, in all your examples, MySQL is NOT using the alias at all but always the real column name.

Normally SQL doesn't allow you to refer to an alias in an ORDER BY clause, but MySQL does allow you to do that in the case where you are using ONLY the alias (not an operation on the alias) like in:

select ccy, myval myval_old, sum(myval) as myval from mytable group  by ccy order by myval desc;

This is allowed just to make some of the most common ORDER BY clauses easy.

The fact that you do:
myval myval_old

doesn't change the name of the column to myval_old, but just names the result of the column as 'myval_old'. You can still use myval in the ORDER BY and this will continue to refer to the original column