--- Dan <[EMAIL PROTECTED]> wrote:
> The "b" in the ORDER BY does not match "x1.b" because it is
> not a simple identifier (according to matchOrderbyToColumn()).
> It does not match either "<identifier>" or "<expr> as <identifier>".
>
> After failing to find a match for "b" in the leftmost SELECT,
> SQLite searches the next leftmost and matches "b" to "b"
> (column 2).
>
> That's how it is at the moment, anyhow.
>
> >
> > http://www.mail-archive.com/sqlite-users@sqlite.org/msg23985.html
> >
>
> Cheers. I'm starting to realise why this little corner of sqlite
> is the way it is...
I believe that there are 2 different issues with the current implementation:
1. The result set column names of a compound SELECT should drop all
table qualifiers, as they've lost all meaning once in a UNION.
i.e., instead of:
sqlite> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2;
x1.b|a
value|value
you should see:
b|a
value|value
as other databases do:
mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY b;
+------+------+
| b | a |
+------+------+
| 2 | 1 |
| 9 | 0 |
+------+------+
mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY x1.b;
ERROR 1054 (42S22): Unknown column 'x1.b' in 'order clause'
2. The compound SELECT's ORDER BY statement elements should only be matched
against the leftmost SELECT. If there is no match in the leftmost
SELECT, then an error should result - even if a match could potentially
be found in non-leftmost SELECTs.
Or do you disagree?
____________________________________________________________________________________
Be a better sports nut! Let your teams follow you
with Yahoo Mobile. Try it now.
http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
