SQLite Forum

IS NULL optimization on NOT NULL constraints breaks on corrupted databases
It seems to me that multiple people in this thread have been fixated on the reproduction example he gave vs the spirit of the report. The original report did see a legitimately corrupted database that did not use the undocumented and undefined behavior of rewriting the schema, but writing the schema was used to provide a simple test case. We always appreciate reproduction steps.

As a result, there was a dependence on NOT NULL on a column that by definition could not be NULL, but due to the bug was now NULL.

This results in a paradox. The schema says "this column can't be NULL" yet "something is NULL". So what is the query planner to do? The QP can do anything at all at that point because by some definition, anything it does will be wrong.

I think what SQLite is doing is perfectly acceptable and the real problem is the "legitimately corrupted database" they were dealing with. I can see an argument for "engine should always sanity check data" but then the query planner can never optimize anything because it can no longer make assumptions about validity. "NOT NULL" seems like a simple case, but one can imagine very complex CHECK constraints that would be even harder to verify at SELECT time for every query.

The most sensible thing is "INSERT is responsible for ensuring constraints are enforced" and that the rest of the engine can safely assume the data meets that criteria. If something is deliberately or accidentally corrupted, I don't think it should be SELECT/UPDATE/DELETE's job to enforce that.

But the question expressing surprise is not unreasonable, and the writable schema was a red herring.