List:General Discussion« Previous MessageNext Message »
From:Dave Torr Date:June 16 2004 10:23pm
Subject:Re: Error 1250 Table from one of the SELECT's cannot be used in global ORDER
clause
View as plain text  
Thanks - this is now on the bugs.mysql.com site as bug 4156 - which as you 
say is actually a bug in the old version. Appreciate your explanation - what 
seems to happen is that even though the SELECT has a fully qualified column 
name (which it has to have as species_id occurs in both tables I am joining) 
the UNION just uses the (unqualified) column name to name the resulting 
table. Makes sense but not obvious!

THanks to all who replied!


>From: SGreen@stripped
>To: "Dave Torr" <dave_torr@stripped>
>CC: mysql@stripped,m.tonies@stripped
>Subject: Re: Error 1250 Table from one of the SELECT's cannot be used in 
>global ORDER clause
>Date: Wed, 16 Jun 2004 09:31:56 -0400
>
>
>RE: "the bug was in 4.0.17, which should not
>have allowed this behaviour!"
>
>That makes sense if you think about it in relation to UNION queries.
>
>There is a major difference between a simple query and the results of a
>UNION query. In a UNION, the first SELECT clause of the first simple query
>determines the column names of the result set and each additional UNIONed
>simple query fills in those columns with more rows of data. Each query can
>come from totally different tables so long as their data types and sizes
>line up with those specified in the first query.  Between each simple query
>participating in the UNION,  all of the accumulated data is basically
>"anonymized" as the columns are no longer bound to a specific table but are
>known only by their UNIONed names.
>
>Let's pretend that the fully qualified column names still existed in the
>resulting UNION dataset. Would that mean that you could sort just part of
>your results? What if the table you specified in your UNION ... ORDER BY
>clause added no records to the final result set? Would any sorting happen
>then? I agree that it was a bug in 4.0.17 to allow that notation in the
>ORDER BY clause of a UNION query.
>
>
>Respectfully,
>Shawn Green
>Database Administrator
>Unimin Corporation - Spruce Pine
>
>
>
>                       "Dave Torr"
>                       <dave_torr@hotmai        To:       
>m.tonies@stripped, mysql@stripped
>                       l.com>                   cc:
>                                                Fax to:
>                       06/16/2004 04:35         Subject:  Re: Error 1250 
>Table from one of the SELECT's cannot be used
>                       AM                        in global ORDER clause
>
>
>
>
>
>
>Don't follow your question - you suggested I use the column name (with no
>table name) as the ORDER BY. I said I can;t do this because I join on that
>column name - this means any references to it MUST be qualified with a
>table
>name.
>
>Anyway - reported this as a 4.1.2 bug (on the grounds that it used to work
>in 4.0.17) and have just been told the bug was in 4.0.17, which should not
>have allowed this behaviour!
>
>
> >From: "Martijn Tonies" <m.tonies@stripped>
> >To: <mysql@stripped>,<dave_torr@stripped>
> >Subject: Re: Error 1250 Table from one of the SELECT's cannot be used in
> >global ORDER clause
> >Date: Wed, 16 Jun 2004 09:06:12 +0200
> >
> >Hi Dave,
> >
> > > Unfortunately species_id is a column I am using for joining so I need
> >the
> > > fully qualified name both in the SELECT and the ORDER BY.
> >
> >What does the join have to do with the ORDER BY?
> >
> >With regards,
> >
> >Martijn Tonies
> >Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
>SQL
> >Server.
> >Upscene Productions
> >http://www.upscene.com
> >
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:
>http://lists.mysql.com/mysql?unsub=1
> >
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
>
>
>
>


Thread
Error 1250 Table from one of the SELECT's cannot be used in global ORDER clauseDave Torr15 Jun
  • Re: Error 1250 Table from one of the SELECT's cannot be used in global ORDER clauseMartijn Tonies15 Jun
Re: Error 1250 Table from one of the SELECT's cannot be used in global ORDERclauseDave Torr16 Jun
  • Re: Error 1250 Table from one of the SELECT's cannot be used in global ORDER clauseMartijn Tonies16 Jun
Re: Error 1250 Table from one of the SELECT's cannot be used in global ORDERclauseDave Torr16 Jun
  • Re: Error 1250 Table from one of the SELECT's cannot be used in global ORDER clauseMartijn Tonies16 Jun
Re: Error 1250 Table from one of the SELECT's cannot be used in globalORDER clauseSGreen16 Jun
Re: Error 1250 Table from one of the SELECT's cannot be used in global ORDERclauseDave Torr17 Jun