Miha Vrhovnik wrote:
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, ...
Why did you expect the latter? DISTINCT in SELECT DISTINCT detects
identical rows, not identical first columns. The three first rows with
an id of 1 differ in the columns you chose not to show (specifically in
the map.d0 column), that's why all three are returned.
The second query is equivalent to
SELECT * FROM t1, map WHERE t1.id = map.idT1 AND id IN (1, 2, 3);
Of course it returns all rows.
Igor Tandetnik
