MySQL Bugs: #4156: Error 1250 Table from one of the SELECT's cannot be used in global ORDER clause

Bug #4156	Error 1250 Table from one of the SELECT's cannot be used in global ORDER clause
Submitted:	16 Jun 2004 0:26	Modified:	16 Jun 2004 13:58
Reporter:	Dave Torr	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.2 ALpha	OS:	Microsoft Windows (Win XP Pro)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
I am getting the above error (the number is wrong - manual says this is
1249) on a statement of the form

(SELECT species_table.species_id,count(*) as c FROM species_table INNER
JOIN
checklist_table USING (species_id,subspecies_id,name_id) GROUP BY
species_id,source HAVING c=1)
UNION
(SELECT species_table.species_id,count(*) as c FROM species_table INNER
JOIN
checklist_table USING (species_id,subspecies_id,name_id) GROUP BY
species_id
HAVING c>1)
ORDER BY species_table.species_id ASC  LIMIT 0,31

(The original statement is more complex - when c=1 I select content from some columns, when c>1 I select a constant string for those columnns - so if there is only one "source" for the "checklist" I return the details, otherwise I just note there are multiple sources.

This works fine on 4.0.17

How to repeat:
Just pass this statement to MyTool linked to a 4.1.2 server. I can supply the full table definitions and the full original statement if required

Thank you for bugreport, but it is not bug, just behaviour changed. 
 
Table name can't be used in global order clause. It was allowed in early 
versions but was fixed as a bug. One can use only fields name in global order 
clause.

Hmmm - it worked well on 4.0.17. Did not realise it was not supported behaviour.  Trouble is I can't use an unqualified column name as the column exists in two tables so needs to be qualified with the table name I guess. Suppose I could rename the column in one table?

global ORDER BY do not belong to any table, it belong to result of union,  
result have only one field with such name, i.e. field is fully  qualified

do you mean that if you drop table name from order by you get an error ?

Actually that works if I just ORDER BY species_id - since species_id occurs in the two tables (it was the column used for the join) I had assumed that it had to be fully qualified (after all I have to qualify it to select it!). So the problem is resolved - thanks