SQLite

View Ticket
Login
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: (text/x-fossil-wiki)
In the test case below, the result seems to be incorrect:

<pre>
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
</pre>

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: (text/x-fossil-wiki)
A bit simpler:

<verbatim>
  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;
</verbatim>

Should return integer value 123, but currently returns NULL.

drh added on 2019-08-03 14:30:38: (text/x-fossil-wiki)
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: (text/x-fossil-wiki)
Fixed by [d465c3ee].