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]. |