Hello,
I'm working on upgrading from sqlite 3.5.7 to 3.6.4 and while running
some regression unit tests in my own app I noticed a couple failures.
Upon further investigation it looks like either a bug has been
introduced into sqlite between 3.5.8 and 3.6.4 or my query is wrong.
Here's some sql to reproduce the issue:
BEGIN;
CREATE TABLE A (id INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT);
INSERT INTO A VALUES(1,'123');
INSERT INTO A VALUES(2,'456');
CREATE TABLE B (id INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT);
INSERT INTO B VALUES(1,1);
INSERT INTO B VALUES(2,2);
CREATE TABLE A_B (B_id INTEGER NOT NULL, A_id INTEGER);
INSERT INTO A_B VALUES(1,1);
INSERT INTO A_B VALUES(2,2);
COMMIT;
The query that I'm executing:
SELECT DISTINCT
CASE
WHEN B.val = 1 THEN 'XYZ'
ELSE A.val
END AS Col1
FROM B
LEFT OUTER JOIN A_B ON B.id = A_B.B_id
LEFT OUTER JOIN A ON A.id = A_B.A_id
ORDER BY Col1 ASC;
I'm expecting the query to return 456 followed by XYZ. But instead it
returns 123 followed by 456. If I remove the DISTINCT clause it returns
what I'm expecting but that doesn't seem like it should matter. I
searched for bugs using the timeline in the wiki but didn't see anything
related to DISTINCT. I'm not sure where else to look...
Is this a bug in sqlite or my query?
Thanks in advance,
Chad
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
