The affinities are not being applied correctly when GROUP BY is in play: sqlite> CREATE TABLE t1(a TEXT); sqlite> INSERT INTO t1 VALUES(123); sqlite> SELECT a=123, a='123' FROM t1; 1|1 sqlite> SELECT a=123, a='123' FROM t1 GROUP BY a; 0|1
Dan. On Nov 12, 2008, at 9:19 AM, Daniel Zingaro wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
