|Title:||MIN() malfunctions for a query with ISNULL condition|
|Last Modified:||2019-08-03 16:51:24|
|Version Found In:||3.29.0|
mrigger added on 2019-08-02 22:32:15:
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].