SQLite

In the test case below, the result seems to be incorrect:

CREATE TABLE t0 (c0, c1);
CREATE INDEX i0 ON t0(c1, c1 + 1 DESC);
INSERT INTO t0(c0) VALUES (1);
SELECT MIN(t0.c1), t0.c0 FROM t0 WHERE t0.c1 ISNULL; -- expected: NULL | 1, actual: NULL | NULL

When omitting the index or the WHERE clause, the query works as expected.

This bug report is similar to [41866dc373], which has already been addressed by a fix.

A bit simpler:

  CREATE TABLE t1 (a, b);
  INSERT INTO t1 VALUES(123, NULL);
  CREATE INDEX i1 ON t1(a, b DESC);
  SELECT MIN(a) FROM t1 WHERE a=123;

Should return integer value 123, but currently returns NULL.

This problem is apparently over 11 years old.

Bisecting shows that this problem was introduced on 2008-07-08 by check-in [fa07c360b708324c] and first appeared in release 3.6.0. The second script above (Dan's reduction) causes an assertion fault starting with the 2008-07-08 check-in if SQLite is compiled with -DSQLITE_DEBUG, but it give the incorrect NULL result if asserts are disabled. The assertion fault went away with check-in [778e91ddb834f608] on 2008-12-21 but the incorrect NULL result has persisted ever since then.

Fixed by [d465c3ee].