SQLite 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 [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 [link] [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?