Thanks for the bug report and for the repro script. This problem appears to have been in the code ever since partial indexes were first introduced in version 3.8.0 (2013-08-26). I have some other things I have to deal with today. I will get to this as soon as I can. There is a ticket at https://www.sqlite.org/src/tktview/1d958d90596593a77420e590a6ab71756484f576
On 12/8/18, Deon Brewis <de...@outlook.com> wrote: > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
