Hi,
I have figured out a somewhat strange behaviour of sub-selects in combination
with a left join.
SQLite version 2.8.9
Enter ".help" for instructions
sqlite> .echo on
sqlite> .read tests.sql
.read tests.sql
--create some base-tables
drop table t1;
create table t1 (b, c);
insert into t1 values ('dog',3);
insert into t1 values ('cat',1);
insert into t1 values ('dog',4);
drop table t2;
create table t2 (c, e);
insert into t2 values (1,'one');
insert into t2 values (2,'two');
insert into t2 values (3,'three');
insert into t2 values (4,'four');
--this select works as expected
select t2.c as c,
e,
b
from t2
left join (
select b,
max(c) as c
from t1
group by b
)
using (c);
c e b
---------- ---------- ----------
1 one cat
2 two NULL
3 three NULL
4 four dog
--this select gets screwed up
select * from (
select t2.c as c,
e,
b
from t2
left join (
select b,
max(c) as c
from t1
group by b
)
using (c)
);
c e b
---------- ---------- ----------
1 one NULL
2 two NULL
3 three NULL
4 four dog
4 four cat
4 four dog
Is this a bug? Am I handling it wrong? Has anybody else seen that kind of
problem?
It should work according to the documentation (I think...). Am I
misunderstanding http://www.sqlite.org/lang.html#select ?
Tim Krah
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
