Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2

[Dan]

On Dec 5, 2007, at 1:24 AM, Joe Wilson wrote:

--- 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?

Not in principle. But I think changes that break backwards
compatibility would be more trouble than they're worth for
something like this. In the absence of clearer guidance
from sql-92, it's probably more important to be compatible
with earlier sqlite versions than with mysql and friends.

Maybe it would be better to document the current behaviour
and move on.

Dan.

______________________________________________________________________ ______________

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]

---------------------------------------------------------------------- -------

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------