SQLite

View Ticket
Login
Ticket Hash: 80256748471a01a680929131b6efddd4df0a69bb
Title: Incorrect use of "WHERE x NOT NULL" index for query with a "WHERE x IS NOT ?" term
Status: Closed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-05-11 13:05:10
Version Found In:
User Comments:
dan added on 2019-05-11 12:53:34:

The following should return 1 row. But instead returns 0:

  CREATE TABLE IF NOT EXISTS t0 (c0);
  CREATE INDEX IF NOT EXISTS i0 ON t0(1) WHERE c0 NOT NULL;
  INSERT INTO t0(c0) VALUES(NULL);
  SELECT * FROM t0 WHERE t0.c0 IS NOT 1; -- returns no row

This issue was discovered by Manuel Rigger and reported on the SQLite mailing list.


dan added on 2019-05-11 12:56:42:

The error goes all the way back to when partial index support was first added to SQLite in version 3.8.0 (2013-08-26). Use the following index to demonstrate the problem with versions that do not support indexes on expressions:

  CREATE INDEX IF NOT EXISTS i0 ON t0(c0, c1) WHERE c0 NOT NULL;


dan added on 2019-05-11 13:05:10:

Fixed by [0ba6d709].