The 'NOT IN' query at the bottom should not return any results. It returns '2'.
Repro: ===== create table foo(y, z); insert into foo values(1,1); insert into foo values(2,2); CREATE INDEX fooZ on Foo(y) where z=1; create table bar(x); insert into bar values(1); insert into bar values(2); select * from bar WHERE x NOT IN (SELECT y from foo); -- this will wrongly return 2. Issue: ===== In the query plan, sqlite chooses to use the FooZ index: explain query plan select * from bar WHERE x NOT IN (SELECT y from foo); QUERY PLAN |--SCAN TABLE bar `--USING INDEX fooZ FOR IN-OPERATOR FooZ is not a valid index for this subquery, it is incomplete due to the partial index expression, with the result is doing a NOT IN over it returns incorrect results. - Deon _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
