RE: [sqlite] ORDER BY of UNION?

[Boris Popov]

Appreciate you looking into this in such detail for me, at least I wasn't
going completely crazy thinking that it should have worked. I'll go ahead
and try to change the code generator to use your suggested workaround, but
will keep an eye on the new ticket 1700:

http://www.sqlite.org/cvstrac/tktview?tn=1700

On a slightly unrelated note, how does one go about getting a login for a
tracking system? Should I stay anonymous?

Cheers!

-Boris

-- 
+1.604.689.0322
DeepCove Labs Ltd.
4th floor 595 Howe Street
Vancouver, Canada V6C 2T5

[EMAIL PROTECTED]

CONFIDENTIALITY NOTICE

This email is intended only for the persons named in the message
header. Unless otherwise indicated, it contains information that is
private and confidential. If you have received it in error, please
notify the sender and delete the entire message including any
attachments.

Thank you.

-----Original Message-----
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 02, 2006 1:52 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] ORDER BY of UNION?

Boris Popov wrote:

>Looks like I'm on my own with this one :)
>
>After a bit of fiddling with it this morning, I figured I had to do this
>
>SELECT DISTINCT * FROM (SELECT t1.ID
> FROM GR_ADDRESS t1
> WHERE t1.ID > 1 UNION ALL SELECT t1.ID
>FROM PERSON t1 ORDER BY t1.ID DESC) 
>
>instead of
>
>SELECT DISTINCT * FROM (SELECT t1.ID
> FROM GR_ADDRESS t1
> WHERE t1.ID > 1 UNION ALL SELECT t1.ID
> FROM PERSON t1) t1 ORDER BY t1.ID DESC
>
>Although it bugs me that the latter seems to be perfectly valid chunk of
SQL
>as far as SQLite documentation is concerned and as far as other DBs are
used
>to doing things. Is this intentional or just one little quirk?
>
>Cheers!
>
>-Boris
>
>  
>
Boris,

I think you have found another example of the problems SQLite has 
handling columns names.

The following log first shows what SQLite thinks the column name is for 
the query without the order by clause (i.e. t1.ID). Then we try to order 
by that column name, with or without the table alias. Both cases result 
in an error. Finally there is a work around that you could use that 
applies an alias to the selected columns in the two tables that are 
combined by the union operation.

SQLite version 3.3.2
Enter ".help" for instructions
sqlite> create table GR_ADDRESS(id, data);
sqlite> create table PERSON(id, data);
sqlite> .mode column
sqlite> .header on
sqlite> insert into gr_address values(1, 10);
sqlite> insert into person values(2, 20);
sqlite> insert into gr_address values(3, 30);
sqlite> SELECT DISTINCT *
   ...> FROM
   ...>     (SELECT t1.ID
   ...>     FROM GR_ADDRESS t1
   ...>     WHERE t1.ID > 1
   ...> UNION ALL
   ...>     SELECT t1.ID
   ...>     FROM PERSON t1)
   ...> t1;
t1.ID
----------
3
2
sqlite> SELECT DISTINCT *
   ...> FROM
   ...>     (SELECT t1.ID
   ...>     FROM GR_ADDRESS t1
   ...>     WHERE t1.ID > 1
   ...> UNION ALL
   ...>     SELECT t1.ID
   ...>     FROM PERSON t1)
   ...> t1 ORDER BY t1.ID DESC;
SQL error: no such column: t1.ID
sqlite> SELECT DISTINCT *
   ...> FROM
   ...>     (SELECT t1.ID
   ...>     FROM GR_ADDRESS t1
   ...>     WHERE t1.ID > 1
   ...> UNION ALL
   ...>     SELECT t1.ID
   ...>     FROM PERSON t1)
   ...> t1 ORDER BY ID DESC;
SQL error: no such column: ID
sqlite> SELECT DISTINCT *
   ...> FROM
   ...>     (SELECT t1.ID as ID
   ...>     FROM GR_ADDRESS t1
   ...>     WHERE t1.ID > 1
   ...> UNION ALL
   ...>     SELECT t1.ID as ID
   ...>     FROM PERSON t1)
   ...> t1 ORDER BY t1.ID DESC;
ID
----------
3
2




You might want to file a bug report ticket. You may also be interested 
in the discussion of a similar problem under ticket 1688.

HTH
Dennis Cote

smime.p7s
Description: S/MIME cryptographic signature