Re: [sqlite] DISTINCT clause bug in 3.6.4?
> 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? A bug. Fixed now: http://www.sqlite.org/cvstrac/chngview?cn=5889 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DISTINCT clause bug in 3.6.4?
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
Re: [sqlite] DISTINCT clause bug in 3.6.4?
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] DISTINCT clause bug in 3.6.4?
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
