Bug #78009 Inconsistent behaviors when column alias name(s) and column name are the same
Submitted: 10 Aug 2015 11:25 Modified: 10 Aug 2015 14:47
Reporter: Su Dylan Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6.22 OS:Any
Assigned to: CPU Architecture:Any

[10 Aug 2015 11:25] Su Dylan
Description:
We run the following SQLs and we see inconsistent behaviors when some of column alias names, column names are the same:

Output:
mysql> drop table if exists t1;
ate table t1(c1 int, c2 int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1(c1 int, c2 int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1,1),(2,0);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select c1, c2 as c1 from t1 order by c1;
ERROR 1052 (23000): Column 'c1' in order clause is ambiguous
mysql> select c1, c1 * -1 as c1 from t1 order by c1;
+------+------+
| c1   | c1   |
+------+------+
|    2 |   -2 |
|    1 |   -1 |
+------+------+
2 rows in set (0.00 sec)

mysql> select c1 * -1 as c1, c1 from t1 order by c1;
+------+------+
| c1   | c1   |
+------+------+
|   -2 |    2 |
|   -1 |    1 |
+------+------+
2 rows in set (0.00 sec)

mysql> select c1 * -1 as c1, c1 from t1 order by c1 desc;
+------+------+
| c1   | c1   |
+------+------+
|   -1 |    1 |
|   -2 |    2 |
+------+------+
2 rows in set (0.00 sec)

mysql> select c1 * -1 as c1, c1 * c1 as c1 from t1 order by c1 desc;
+------+------+
| c1   | c1   |
+------+------+
|   -1 |    1 |
|   -2 |    4 |
+------+------+
2 rows in set (0.00 sec)

mysql> select  c1 * c1 as c1, c1* -1 as c1 from t1 order by c1 desc;
+------+------+
| c1   | c1   |
+------+------+
|    4 |   -2 |
|    1 |   -1 |
+------+------+
2 rows in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.22-log |
+------------+
1 row in set (0.00 sec)

=====
From document, we got the following description:
https://dev.mysql.com/doc/refman/5.6/en/select.html

--Quote start--
MySQL resolves unqualified column or alias references in ORDER BY clauses by searching in the select_expr values, then in the columns of the tables in the FROM clause. 
--Quote end--

However, we are still not quite sure about the behavior in the following three outputs:
mysql> select c1, c2 as c1 from t1 order by c1;
ERROR 1052 (23000): Column 'c1' in order clause is ambiguous
--Question: Here, message with "ambiguous" is issued, while the following SQLs give result. Why is it?

mysql> select c1, c1 * -1 as c1 from t1 order by c1;
+------+------+
| c1   | c1   |
+------+------+
|    2 |   -2 |
|    1 |   -1 |
+------+------+
2 rows in set (0.00 sec)

mysql> select c1 * -1 as c1, c1 * c1 as c1 from t1 order by c1 desc;
+------+------+
| c1   | c1   |
+------+------+
|   -1 |    1 |
|   -2 |    4 |
+------+------+
2 rows in set (0.00 sec)

Please advise how to understand the results for the above 3 SQLs. Thanks.

How to repeat:

drop table if exists t1;
create table t1(c1 int, c2 int);
insert into t1 values(1,1),(2,0);
select c1, c2 as c1 from t1 order by c1;
select c1, c1 * -1 as c1 from t1 order by c1;
select c1 * -1 as c1, c1 from t1 order by c1;
select c1 * -1 as c1, c1 from t1 order by c1 desc;
select c1 * -1 as c1, c1 * c1 as c1 from t1 order by c1 desc;
select  c1 * c1 as c1, c1* -1 as c1 from t1 order by c1 desc;

Suggested fix:
We want to be aware of the facts of the design logic for the mentioned scenarios.
[10 Aug 2015 14:47] Sinisa Milivojevic
Hi,

Thank you for your report. However, this is not a bug. Our manual is correct and, hence, the behavior is easy to explain. If there are expressions in the SELECT list, then other clauses in the query will first look at the expressions aliases for the resolution of the field name. After the expressions have been searched for, and alias is not found, then table(s) column names are searched.

Hence, in the examples that worked you always had an aliased expression, while in the examples that did not work, there were no aliases !