Re: [sqlite] SQLite 3.7.5 : same query written using NOT IN and EXCEPT does not give out same results
On Tue, Feb 22, 2011 at 14:17, Richard Hippwrote: > > The query is really more like this: > > SELECT DISTINCT COALESCE(a.xxx, b.value) value > FROM tbl1 a > LEFT OUTER JOIN tbl2 b > ON a.zzz = b.ttt > WHERE value NOT IN ( > SELECT DISTINCT ggg > FROM tbl3 > ); > > The "value" on the left-hand side of the NOT IN operator is ambiguous: Does > it refer to the first column of output or to the "value" column of the "b" > table? SQLite chooses the latter. Thank you for your answer. Benoit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.5 : same query written using NOT IN and EXCEPT does not give out same results
On Tue, Feb 22, 2011 at 5:22 AM, Benoit Mortgatwrote: > Hello, > > I have come across a strange behaviour of SQLite 3.7.5. > > The following query: > > SELECT DISTINCT COALESCE(a.xxx, b.yyy) value > FROM tbl1 a > LEFT OUTER JOIN tbl2 b >ON a.zzz = b.ttt > EXCEPT > SELECT DISTINCT ggg value > FROM tbl3; > > will not return any results (which seems to be correct). > > However, when I write it this way: > > SELECT DISTINCT COALESCE(a.xxx, b.yyy) value > FROM tbl1 a > LEFT OUTER JOIN tbl2 b >ON a.zzz = b.ttt > WHERE value NOT IN ( >SELECT DISTINCT ggg > FROM tbl3 > ); > The query is really more like this: SELECT DISTINCT COALESCE(a.xxx, b.value) value FROM tbl1 a LEFT OUTER JOIN tbl2 b ON a.zzz = b.ttt WHERE value NOT IN ( SELECT DISTINCT ggg FROM tbl3 ); The "value" on the left-hand side of the NOT IN operator is ambiguous: Does it refer to the first column of output or to the "value" column of the "b" table? SQLite chooses the latter. You can fix it in a couple of ways: SELECT DISTINCT COALESCE(a.xxx, b.value) valueX FROM tbl1 a LEFT OUTER JOIN tbl2 b ON a.zzz = b.ttt WHERE valueX NOT IN ( SELECT DISTINCT ggg FROM tbl3 ); Or SELECT DISTINCT COALESCE(a.xxx, b.value) value FROM tbl1 a LEFT OUTER JOIN tbl2 b ON a.zzz = b.ttt WHERE COALESCE(a.xxx, b.value) NOT IN ( SELECT DISTINCT ggg FROM tbl3 ); > > I get results (which are wrong). > > I could send a samble database with full query to a developer if > needed in order to reproduce that. > Is this known bug? > > Thank you, > Benoit > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.5 : same query written using NOT IN and EXCEPT does not give out same results
On Tue, Feb 22, 2011 at 5:22 AM, Benoit Mortgatwrote: > I could send a samble database with full query to a developer if > needed in order to reproduce that. > Please do send the sample database and the full queries. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite 3.7.5 : same query written using NOT IN and EXCEPT does not give out same results
Hello, I have come across a strange behaviour of SQLite 3.7.5. The following query: SELECT DISTINCT COALESCE(a.xxx, b.yyy) value FROM tbl1 a LEFT OUTER JOIN tbl2 b ON a.zzz = b.ttt EXCEPT SELECT DISTINCT ggg value FROM tbl3; will not return any results (which seems to be correct). However, when I write it this way: SELECT DISTINCT COALESCE(a.xxx, b.yyy) value FROM tbl1 a LEFT OUTER JOIN tbl2 b ON a.zzz = b.ttt WHERE value NOT IN ( SELECT DISTINCT ggg FROM tbl3 ); I get results (which are wrong). I could send a samble database with full query to a developer if needed in order to reproduce that. Is this known bug? Thank you, Benoit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users