View Ticket
Ticket Hash: 8bf76328ac940d529358dcff19f4ad11ce6abd7f
Title: NOT NULL auxiliary column in rtree table malfunctions
Status: Fixed Type: Documentation
Severity: Cosmetic Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-12-07 12:15:01
Version Found In: 3.30.0
User Comments:
mrigger added on 2019-12-06 00:15:58: (text/x-fossil-wiki)
Consider the following example:

CREATE VIRTUAL TABLE rt0 USING rtree(c0, c1, c2, +c3 NOT NULL);
INSERT INTO rt0(c3) VALUES(NULL); -- unexpected: inserting NULL succeeds

It seems that the NOT NULL constraint is neither enforced, nor rejected. This can result in incorrect results since the constraint still seems to be used for optimization:

SELECT SUM(count) FROM (SELECT 0 IN (rt0.c3) ISNULL as count FROM rt0); -- expected: 0, actual: 1

drh added on 2019-12-06 03:04:27: (text/x-fossil-wiki)
Documentation enhanced here: [https://www.sqlite.org/docsrc/info/c3ab325994a8f495]

The expectation that "SELECT SUM(count) FROM (SELECT 0 IN (rt0.c3) ISNULL as count FROM rt0);" should return 0 is incorrect.

  *  Because rt0.c3 is NULL, "0 in (rt0.c3)" evaluates to NULL
  *  "NULL ISNULL" evaluates to 1.  Hence "0 in (rt0.c3) IS NULL" evaluates to 1.
  *  Hence the "count" column of the subquery is 1.
  *  The sum() of a single value 1 is 1.

mrigger added on 2019-12-06 09:39:22: (text/x-fossil-wiki)
Thanks for clarifying that constraints are ignored! I should have reported that the following is unexpected:

SELECT 0 in (rt0.c3) ISNULL FROM rt0; -- expected: 1, actual: 0

Only when removing the NOT NULL constraint, does the query compute TRUE.

mrigger added on 2019-12-07 11:12:51: (text/x-fossil-wiki)
A follow up on this: I think that the documentation change is misleading, since it suggests that NOT NULL is currently ignored in every context. However, the updated test case above demonstrates that it actually influences the query's result. If this cannot be addressed easily, it might be an option to change "are silently ignored" to explaining that queries operating on such columns have an undefined result.

drh added on 2019-12-07 12:15:01: (text/x-fossil-wiki)
Auxiliary columns now also ignore type affinity.  This fixes the problem.
The documentation has been suitably updated.