(text/x-fossil-wiki)
The following script returns no rows before the partial index is created,
and one row afterwards. The correct answer is no rows:
<blockquote><verbatim>
CREATE TABLE t1(a,b);
INSERT INTO t1 VALUES(1,1);
INSERT INTO t1 VALUES(2,2);
CREATE TABLE t2(x);
INSERT INTO t2 VALUES(1);
INSERT INTO t2 VALUES(2);
SELECT 'one', * FROM t2 WHERE x NOT IN (SELECT a FROM t1);
CREATE INDEX t1a ON t1(a) WHERE b=1;
SELECT 'two', * FROM t2 WHERE x NOT IN (SELECT a FROM t1);
</verbatim></blockquote>
This problem appears to have been in the code since partial indexes were first
introduced with version 3.8.0 (2013-08-26). The problem was first reported on
the user mailing list by Deon Brewis.
|