On 2018-01-06 15:22, x wrote:
Because the field is not NULL in Luuk's definition and NULL values are
not covered by the INDEX. SQLite assumes that you know what you are
doing and tries to find NULL values by full scan.

The ID field in my definition is also not null.

If so, then you should obtain results mentioned by Luuk:

sqlite> Explain query plan select ID from Tbl where ID is null order by ID;
0|0|0|SCAN TABLE Tbl

I have the same results. ``SCAN TABLE Tbl USING COVERING INDEX ...'' is returned in case of not ``NOT NULL'' field.

``SCAN TABLE Tbl USING COVERING INDEX ...'' means that your index is
used. Order by does not apply as it is the same as PK. Scanning by using
PK results in ORDERed BY PK records. There is no need to use separate
INDEX for ORDER BY clause.

I’m not sure what you’re saying there Cezary. I recognise that the 
(non-existent) result set will all be NULLs and the order is therefore 
meaningless but if the query planner recognises that fact why does it not 
recognise that there will be no result set.

Order is meaningful but it is the same as order of a table scanning process -- this is why ORDER BY is ``ignored'' -- not because there will be an empty result set. If you change to ORDER BY ID*2, then you will see that temporary index will be created. The same index is used for scanning and ordering, so there is no need to use it twice.

If I run explain query plan select ID from Tbl where 0; // that’s where zero

I again get the result


Again, I have the sole ``SCAN TABLE'' (without index -- in both cases: NULLs allowed and NOT NULL). Your result is obtained as if it was ORDER BY clause.

However the EXPLAIN for the same query returns

addr        opcode           p1    p2    p3   p4    p5    comment

0      Init          0      8      0              00   Start at 8

1      Goto               0      7      0             00

2      OpenRead      1      109 0      k(2,,)       00    root=109 iDb=0; tID

3      Rewind           1      7      1      0      00

4      IdxRowid        1      1      0             00    r[1]=rowid

5      ResultRow      1      1      0             00    output=r[1]

6      Next                1      4      0             01

7      Halt         0      0      0              00

8      Transaction    0      0      392 0      01        usesStmtJournal=0

9      Goto               0      1      0             00

which, if I’m reading it correctly, executes 0, 8, 9, 1 & 7 (Halt) and so comes 
up with the empty result set in a few milliseconds. That contrasts with the 
EXPLAINs of the IS NULL queries mentioned earlier which do an actual table scan.
Indeed -- I have nearly the same: SQLite jumps directly to Halt in case of WHERE 0, except that I have Column instead of IdxRowid. Could you provide your table's CREATE command? AFAIR you are using SQLite 3.21, are not you?

-- best regards

Cezary H. Noweta
sqlite-users mailing list

Reply via email to