SQLite Forum

IS NULL optimization on NOT NULL constraints breaks on corrupted databases
Create a simulated corrupted foo.db with
create table t(id integer primary key not null,v integer);
insert into t values(1,NULL);
PRAGMA writable_schema=ON;
update sqlite_master set sql='create table t(id integer primary key not null,v integer not null);' where name='t';
with sqlite 3.35 or later,
./sqlite3 foo.db "select id, v from t where v is not null"
The expected result would have for it to not return that row, as in previous versions.

This causes a regression in the GDAL open source project which trusts the "v is not null" and dereferences values in that field without testing them against NULL.

git bisects on the git mirror points to the following commit:

commit 8ddf686267c58ee0e519cfd9024f177e606be0ab
Author: dan <Dan Kennedy>
Date:   Fri Feb 26 20:14:32 2021 +0000

    Attempt to optimize "x IS NULL" and "x IS NOT NULL" expressions when x is a column with a NOT NULL constraint.
    FossilOrigin-Name: 5ecd842555009ce27ee6390325ac5c2504143474b12b730933f0833b3dad788a