> Le 8 déc. 2018 à 09:02, Deon Brewis <de...@outlook.com> a écrit : > > 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.
Indeed. As well as, logically: select * from bar WHERE x IN (SELECT y from foo); -- this will wrongly return 1. As expected a simple WHERE 1 in the subselect cancels the wrong index selection: select * from bar WHERE x NOT IN (SELECT y from foo WHERE 1); -- OK, no results -- Best Regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
