On 3/9/16, Clemens Ladisch <clemens at ladisch.de> wrote: > In the question > <http://stackoverflow.com/questions/35891605/why-do-i-receive-an-unexpected-result> > David Paton asks about a query like this: > > create table a(id integer primary key); > create table b(id integer primary key); > insert into a values (1), (2); > insert into b values (1); > > select id from a where id not in (select a.id from b); > > which outputs one result (2), although the expected result would be > empty. >
Thanks for the bug report. The fix is here: https://www.sqlite.org/src/info/1ed6b06ea3c432f9 The problem has been in the code since before SQLite 3.6.1 in 2008. Since nobody else has bumped into it in all that time, we'll wait to publish the fix as part of the next scheduled release (3.12.0) which should occur in 3-4 weeks. -- D. Richard Hipp drh at sqlite.org