MySQL Bugs: #4302: ambiguous order by when renamed column is identical to another in result

Bug #4302	ambiguous order by when renamed column is identical to another in result
Submitted:	27 Jun 2004 13:29	Modified:	30 Sep 2004 14:08
Reporter:	Scott MacVicar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.2-alpha-nt	OS:	Windows (Windows 2000)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
Change between 4.0.20 and 4.1.2 have resulted in an error when ordering on a column that is present in both tables even if the second instance in the result is renamed.

This was possibly the result of the fix for http://bugs.mysql.com/bug.php?id=3188

How to repeat:
DROP TABLE IF EXISTS tbl1;
CREATE TABLE tbl1 (
	col1 SMALLINT NOT NULL,
	col SMALLINT NOT NULL,
	PRIMARY KEY (col1)
);
DROP TABLE IF EXISTS tbl2;
CREATE TABLE tbl2 (
	col2 SMALLINT NOT NULL,
	col SMALLINT NOT NULL,
	PRIMARY KEY (col2)
);

SELECT tbl1.*, tbl2.col AS tbl2_col FROM tbl1
LEFT JOIN tbl2 ON (tbl1.col1 = tbl2.col2)
ORDER BY col;

Fixed in 4.1.4

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:

Last small issue related to this bug was fixed. According to SQL standard, queries like 
"select t1.a as col from t1, t2 order by a" should return an error if both tables contain field a.
Now MySQL behaves according to standard in this repect.

ChangeSet 1.2017.23.1 2004/09/30 16:28:17 dlenev@brandersnatch.localdomain
  Final solution for bug# 4302 "Ambiguos order by when renamed column is
  identical to another in result"