Hi, In case it helps, I've narrowed this down somewhat; it also happens with this far simpler query:
sqlite> select b.val, case when b.val = 1 then 'xyz' else b.val end as col1 from b; val|col1 1|xyz 2|2 sqlite> select distinct b.val, case when b.val = 1 then 'xyz' else b.val end as col1 from b; val|col1 1|1 2|2 Thanks, Dan Slater, Chad wrote: > 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
