SQLite User Forum

query plan for ”is null” on column defined as ”not null”
Login

query plan for "is null" on column defined as "not null"

(1) By anonymous on 2021-04-21 17:01:22 [source]

I created a table with a column specified as not null (and an index for that column).  Doing a query on that column where a where clause that as "is null" seems like it would be able to do an order(1) check since no rows can match. 

Instead it seems to do a scan.

Creating the same test but omitting the "is null" column attribute leaves it to use the index.

Perhaps my setup is wrong here but this seems confusing.
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t (foo not null);
INSERT INTO t VALUES(1);
INSERT INTO t VALUES(2);
INSERT INTO t VALUES(3);
INSERT INTO t VALUES(4);
INSERT INTO t VALUES(5);
CREATE TABLE r (foo);
INSERT INTO r VALUES(1);
INSERT INTO r VALUES(2);
INSERT INTO r VALUES(3);
INSERT INTO r VALUES(4);
INSERT INTO r VALUES(5);
CREATE INDEX idx on t(foo);
CREATE INDEX idxr on r(foo);
COMMIT;
sqlite> 
sqlite> explain query plan select * from r where foo is null;
QUERY PLAN
`--SEARCH TABLE r USING COVERING INDEX idxr (foo=?)
sqlite> explain query plan select * from t where foo is null;
QUERY PLAN
`--SCAN TABLE t
sqlite>