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

[Dr Gerard Hammond]

I have reported it as a bug - ticket is 
http://www.sqlite.org/cvstrac/tktview?tn=2822

It appears as though the /src/select.c (Line1499) changed
from:
   if( iCol<0 && mustComplete ){
to:
    }else if( mustComplete ){

in version 1.336 of this file - 
http://www.sqlite.org/cvstrac/filediff?f=sqlite/src/select.c&v1=1.335&v2=1.336

And this change results in this bug.


On 04/12/2007, at 4:59 AM, Joe Wilson wrote:

--- Marco Bambini <[EMAIL PROTECTED]> wrote:

Starting from version 3.4.2 I receive errors with queries like:

SELECT a.field FROM a UNION ALL SELECT b.field FROM b ORDER BY a.field

or even

SELECT a.field FROM a UNION ALL SELECT a.field FROM a ORDER BY a.field

error is:
ORDER BY term number 1 does not match any result column

Tables are created by:
CREATE TABLE a (field);
CREATE TABLE b (field);

Please note that the above queries worked fine with sqlite 3.2.x or
3.3.x.
Any idea?

You probably know the workarounds:

 SELECT a.field FROM a UNION ALL SELECT b.field FROM b ORDER BY 1;

or

SELECT a.field AS x FROM a UNION ALL SELECT b.field FROM b ORDER BY x;

but it's odd that this one doesn't work as well:

 create table t1(a);
 create table t2(b);

 select t1.a from t1 union all select t2.b from t2 order by a;

 SQL error: ORDER BY term number 1 does not match any result column

Cheers.

--

Dr Gerard Hammond
Garvan Institute of Medical Research






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