Igor Tandetnik wrote:
I seem to have stumbled on a bug in SQLite.
create table Campaigns (id integer primary key, accountId integer, name
varchar);
create table CampaignStats (id integer primary key, campaignId integer);
insert into Campaigns values(1, 1, 'A');
insert into Campaigns values(2, 1, 'B');
select c.*
from Campaigns c join (select 1) on (accountId=1)
left join CampaignStats cs on (c.id = cs.campaignId);
1|1|A
2|1|B
select c.*
from Campaigns c join (select 1) on (accountId=1)
left join CampaignStats cs on (c.id = cs.campaignId)
order by c.name;
<no rows returned>
fwiw...
D:\user\pkishor\ui>sqlite3 foo.db
SQLite version 3.2.1
Enter ".help" for instructions
sqlite> create table Campaigns (id integer primary key, accountId
integer, name
varchar);
sqlite> create table CampaignStats (id integer primary key, campaignId
integer);
sqlite> insert into Campaigns values(1, 1, 'A');
sqlite> insert into Campaigns values(2, 1, 'B');
sqlite> select c.*
...> from Campaigns c join (select 1) on (accountId=1)
...> left join CampaignStats cs on (c.id = cs.campaignId);
1|1|A
2|1|B
sqlite> select c.*
...> from Campaigns c join (select 1) on (accountId=1)
...> left join CampaignStats cs on (c.id = cs.campaignId)
...> order by c.name;
1|1|A
2|1|B
sqlite>
It seems a combination of "join (select 1)" (admittedly weird, but there
are reasons I would like to be able to do this), "left join" and "order
by" confuses the engine. Removing any one of the three makes the query
work as expected.
Running SQLite 3.2.7 on Windows, from precompiled binaries.
Igor Tandetnik
