/ View Ticket
Ticket Hash: 71e183cab6c0444ac951062c262a6075c65938ad
Title: MIN() malfunctions for a query with ISNULL condition
Status: Closed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-08-03 16:51:24
Version Found In: 3.29.0
User Comments:
mrigger added on 2019-08-02 22:32:15:

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

dan added on 2019-08-03 13:44:23:

A bit simpler:

  CREATE TABLE t1 (a, b);
  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.

drh added on 2019-08-03 14:30:38:

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.

dan added on 2019-08-03 16:51:24:

Fixed by [d465c3ee].