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 16:49:34 [link] [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>

(2) By Richard Hipp (drh) on 2021-04-21 17:38:52 in reply to 1 [source]

It does. The optimization is simply not shown in the EXPLAIN QUERY PLAN output. If you do an ordinary EXPLAIN you can see it:

addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     8     0                    0   Start at 8
1     Goto           0     7     0                    0   
2     OpenRead       0     2     0     1              0   root=2 iDb=0; t
3     Rewind         0     7     0                    0   
4       Column         0     0     1                    0   r[1]=t.foo
5       ResultRow      1     1     0                    0   output=r[1]
6     Next           0     4     0                    1   
7     Halt           0     0     0                    0   
8     Transaction    0     0     4     0              1   usesStmtJournal=0
9     Goto           0     1     0                    0   

Notice how the Goto on instruction 1 bypasses the table scan.

(3) By Mark Wagner (markxwagner) on 2023-03-26 05:55:34 in reply to 2 [link] [source]

That's good to know. But having to fallback to the byte code to know if there's a full table scan for a given operation seems suboptimal.

In other words, this still seems like a bug. Not with the actual code generated, but with the results printed by explain query plan.

Thoughts?