MySQL Bugs: #4098: UNION: ORDER BY now producing error when using tablename specifier

Bug #4098	UNION: ORDER BY now producing error when using tablename specifier
Submitted:	10 Jun 2004 22:28	Modified:	11 Jun 2004 0:05
Reporter:	Michael Winston	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.20	OS:	MacOS (Mac OS X)
Assigned to:		CPU Architecture:	Any

Description:
This is more a backwards-compatibility issue.

When using a global "order by" in a union, specifying the tablename produces an error (which it 
probably should since the union-ed table is its own creature).  This did not happen with older versions 
(tested with 4.0.17).

How to repeat:

Use the following command in 4.0.20:
(select tbl.col from tbl where 1 limit 1) union (select tbl.col from tbl where 1 limit 1) order by tbl.col;

However, in 4.0.17 no error is produced and MySql knows which col to order by.

Also, by simply not specifying the table, the correct result is produced:
(select col from tbl where 1 limit 1) union (select col from tbl where 1 limit 1) order by col;

Suggested fix:
Restore the "auto-sensing" of which column to sort by for optimal backwards compatibility.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

it's not a bug. On the opposite the fact that MySQL was accepting table name in the global order by in union was considered a bug, and it was fixed. So it's a deliberate change.

Having table name in global order by makes no sense, as far as I can see.
Why do you need it ?

Thank you for your quick response.

I realize that the "new" way is clearly the correct one.  I tried  to write up my "bug" report to reflect that.  
I don't actually *need* it to work that way.  We have coders that have used the 'order by tbl.col' syntax 
in their queries.  Their expectation was that this would continue working through version changes.  
Since it doesn't, they'll have to go back and change their code.

There might be others out there who were relying on their queries behaving a certain way and now have 
them behaving a different way.