/ View Ticket
Login
Ticket Hash: 1d958d90596593a77420e590a6ab71756484f576
Title: Incorrect result with NOT IN operator and partial index
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2018-12-08 20:30:39
Version Found In: 3.26.0
User Comments:
drh added on 2018-12-08 13:06:23: (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.