SQLite

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

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.

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.

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.

Auxiliary columns now also ignore type affinity. This fixes the problem. The documentation has been suitably updated.