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
