MySQL Bugs: #5866: Inconsistent column 'ambiguousness'

Bug #5866	Inconsistent column 'ambiguousness'
Submitted:	3 Oct 2004 10:36	Modified:	9 Oct 2004 10:09
Reporter:	Olaf van der Spek (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	MySQL 4.0.21-nt	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
The first select query causes no error.
The second select query causes this error:
ERROR 1052: Column: 'a' in order clause is ambiguous

I think that either both queries should not cause an error or both queries should cause an error.

How to repeat:
CREATE TABLE ta 
(
  a int(11),
  b int(11)
);

CREATE TABLE tb 
(
  a int(11),
  b int(11)
);

select tb.*, tb.a a, tb.b b from ta inner join tb order by a;
select tb.*, tb.a a, tb.b b from ta inner join tb order by a + b;

MySQL behaves consistency, but it is a bit obscure.

In ORDER BY clause it is allowed to use 
* column name  e.g. SELECT ... ORDER BY tb.a
* alias e.g. SELECT tb.a AS x ... ORDER BY x
* expression  or function possibly depending on columns

The problem you observe is due to fact that expression in ORDER BY can not contain aliases.

SELECT tb.a AS x FROM tb ORDER BY x; --> correct
SELECT tb.a AS x FROM tb ORDER BY (x + 0); --> wrong

In your example

select tb.*, tb.a a, tb.b b from ta inner join tb order by a + b;

(a + b) in ORDER BY can *not* use aliases you have set in SELECT part.
It is only allowed to use column names from tables. In your case both tables have columns  called a and this is what causes the error.

You can observe this error with:

mysql> select 1 from ta, tb order by a;
ERROR 1052 (23000): Column 'a' in order clause is ambiguous

As for the first query it succeeds because there is no ambiguity in select part.

select tb.*, tb.a a, tb.b b from ta inner join tb order by a;

Here result column names are (a, b, a, b) but alias `a` is an alias of the column `a` so it doesn't matter which one will be used for sorting.

Try to add another alias `a` and the same error will occur again
Let's simplify the example:

select ta.a, ta.a as a from ta order by a; -- this is OK

mysql> select ta.a, ta.b as a from ta order by a; -- this is wrong
ERROR 1052 (23000): Column 'a' in order clause is ambiguous

Why are functions in ORDER BY not allowed to depend on aliases?

And does this mean MySQL evaluates this expression twice?
What if it's a fairly expensive expression?

Hmm, even worse, what if you want to order by a group function?

Could you reopen this as feature request?

MySQL does not evaluate aggregate functions twice.
Your note about double evaluation of expressions, if they're used in ORDER BY seems to be valid.
Note, that the same thing happens if you use an expression twice in SELECT list:

SELECT expensive_func(a), expensive_func(a)+1 from t; -- expensive_func will be evaluated twice.
You can't use aliases to solve the problem here as well:
SELECT expensive_func(a) as ef, ef+1 from t; -- this won't parse

Could you reopen this as feature request?