SQLite

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.

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;

Fixed by [0ba6d709].