Re: [sqlite] bug in SELECT DISTINCT ?

2005-09-17 Thread Matt Wilson 
On Sat, Sep 17, 2005 at 03:23:04PM +0200, Miha Vrhovnik wrote:
> 
> both
> SELECT DISTINCT id, * FROM t1, map WHERE t1.id = map.idT1;
> and
> SELECT * FROM t1, map WHERE t1.id = map.idT1 AND id IN (SELECT DISTINCT idT1 
> AS id FROM map WHERE d0 = 2);

sqlite> SELECT id, * FROM t1, map WHERE t1.id = map.idT1 GROUP BY id;
1|1|1|dd|1|2
2|2|1|dd|2|2
3|3|1|dd|3|2

Not sure if this is really what you want, because you're losing data
from the map as rows are excluded.
-- 
Matt Wilson
rpath, Inc.
[EMAIL PROTECTED]

[sqlite] bug in SELECT DISTINCT ?

2005-09-17 Thread Miha Vrhovnik 
Hi!

I think there is a bug in SELECT DISTINC. I'm using sqlite 3.2.5 dll

CREATE TABLE t1(id INTEGER PRIMARY KEY, d0 INTEGER, d1 TEXT);
CREATE TABLE map(idT1 INTEGER , d0 INTEGER);

INSERT INTO t1 VALUES(1, 1, 'dd');
INSERT INTO t1 VALUES(2, 1, 'dd');
INSERT INTO t1 VALUES(3, 1, 'dd');

INSERT INTO map VALUES(1, 2);
INSERT INTO map VALUES(1, 3);
INSERT INTO map VALUES(1, 4);
INSERT INTO map VALUES(2, 2);
INSERT INTO map VALUES(3, 2);

both
SELECT DISTINCT id, * FROM t1, map WHERE t1.id = map.idT1;
and
SELECT * FROM t1, map WHERE t1.id = map.idT1 AND id IN (SELECT DISTINCT idT1 AS 
id FROM map WHERE d0 = 2);

return
1, ...
1, ...
1, ...
2, ...
3, ...

instead of
1, ...
2, ...
3, ...

Regards,
Miha