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: [EMAIL PROTECTED]
To: "Dave Torr" <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED],[EMAIL PROTECTED]
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"
<[EMAIL PROTECTED] To:
[EMAIL PROTECTED], [EMAIL PROTECTED]
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" <[EMAIL PROTECTED]>
>To: <[EMAIL PROTECTED]>,<[EMAIL PROTECTED]>
>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/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
