/ View Ticket
Login
Ticket UUID: 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:

The following script returns no rows before the partial index is created, and one row afterwards. The correct answer is no rows:

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);

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.