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?