Thanks a lot for the quick fix! I'm curious how that test that you added works? i.e. What causes the test to fail if the results are wrong? (Sorry, I know almost nothing about the SQLITE test framework). https://www.sqlite.org/src/info/15bc915dd7ae25bf
- Deon -----Original Message----- From: drhsql...@gmail.com <drhsql...@gmail.com> On Behalf Of Richard Hipp Sent: Saturday, December 8, 2018 5:11 AM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Cc: Deon Brewis <de...@outlook.com>; o...@integral.be Subject: Re: [sqlite] SQLITE gives incorrect results for 'NOT IN' query if partial index exists 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
