/ Ticket Change Details
Login
Overview

Artifact ID: 17e6b575655365a1da3d077246c0da9433ff5a2cd156f719f04d997dbd782c29
Ticket: 1d958d90596593a77420e590a6ab71756484f576
Incorrect result with NOT IN operator and partial index
User & Date: drh 2018-12-08 13:06:23
Changes

  1. Change foundin to "3.26.0"
  2. Change icomment to:

    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.

  3. Change login to "drh"
  4. Change mimetype to "text/x-fossil-wiki"
  5. Change severity to "Severe"
  6. Change status to "Open"
  7. Change title to:

    Incorrect result with NOT IN operator and partial index

  8. Change type to "Code_Defect"