[sqlite] bug in SELECT DISTINCT ?

[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, 'dddddd');
INSERT INTO t1 VALUES(2, 1, 'dddddd');
INSERT INTO t1 VALUES(3, 1, 'dddddd');

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