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.
