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

[16 Jun 2004 0:26] Dave Torr
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
[16 Jun 2004 9:55] Oleksandr Byelkin
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.
[16 Jun 2004 10:42] Dave Torr
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?
[16 Jun 2004 12:55] Oleksandr Byelkin
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
[16 Jun 2004 13:58] Sergei Golubchik
do you mean that if you drop table name from order by you get an error ?
[17 Jun 2004 0:20] Dave Torr
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