On Tue, Feb 22, 2011 at 5:22 AM, Benoit Mortgat <mort...@gmail.com> wrote:
> 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