MySQL Bugs: #14662: ORDER BY on column of a view, with an alias of the same column causes ambiguous

Bug #14662	ORDER BY on column of a view, with an alias of the same column causes ambiguous
Submitted:	4 Nov 2005 21:41	Modified:	23 Nov 2005 14:36
Reporter:	Mark Leith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.16-BK	OS:	Linux (Linux, Any)
Assigned to:	Timour Katchaounov	CPU Architecture:	Any

Description:
Using ORDER BY on a column from a view, when also selecting the column normally, and via an alias, causes a "Column 'x' in order clause is ambiguous" error. 

How to repeat:
DROP TABLE t1;
DROP VIEW v1;

CREATE TABLE t1 (i INT);
CREATE DEFINER = root@'%' VIEW v1 (i) AS SELECT i FROM t1;

SELECT v1.i AS i1, i FROM v1 ORDER BY v1.i;

Somewhat more generic test case:

DROP TABLE t1;
DROP VIEW v1;

CREATE TABLE t1 (i INT);
CREATE VIEW v1 (i) AS SELECT i FROM t1;

SELECT v1.i AS i1, i FROM v1 ORDER BY v1.i;
SELECT t1.i AS i1, i FROM t1 ORDER BY t1.i;

This bug only occurs when the MERGE algorithm is used.

DROP TABLE t1;
DROP VIEW v1;

CREATE TABLE t1 (i INT);
CREATE ALGORITHM=TEMPTABLE VIEW v1 (i) AS SELECT i FROM t1;

SELECT v1.i AS i1, i FROM v1 ORDER BY v1.i;

DROP VIEW v1;
CREATE ALGORITHM=MERGE VIEW v1 (i) AS SELECT i FROM t1;
SELECT v1.i AS i1, i FROM v1 ORDER BY v1.i;

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32144

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32173

Fix pushed into 5.0.17.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented fix for 5.0.17.