Thanks Cezary but I’m none the wiser.


>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.



>``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.



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



I again get the result



SCAN TABLE Tbl USING COVERING INDEX XXX



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.



Regards



Tom













________________________________
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Cezary H. Noweta <c...@poczta.onet.pl>
Sent: Saturday, January 6, 2018 1:01:13 PM
To: SQLite mailing list
Subject: Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

Hello,

On 2018-01-06 13:33, x wrote:
>> sqlite> Explain query plan select ID from Tbl where ID is null order by ID;
>> selectid|order|from|detail
>> 0|0|0|SCAN TABLE Tbl
>
> Luuk, I’m also using 3.21. Presumably if you give your Tbl a secondary index 
> XXX then the explain query plan will match mine (SCAN TABLE Tbl USING 
> COVERING INDEX XXX). Your example muddies the water further though. Why is it 
> scanning an entire table when it could scan the pk?


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.

>>> It doesn’t even use the ID pk despite the fact it’s the requested order by??
>> That is explained by Cezary, if the explanation was unclear answer to his 
>> post...
>
> I wasn’t specifically replying to your post, I just clicked reply to the 
> latest reply. I don’t see where Cezary explains it though.

In your example (a field allowing NULLs) PK is used:

2     Null           0     1     0                    00
3     Affinity       1     1     0     D              00
4     SeekGE         1     9     1     1              00
5       IdxGT          1     9     1     1              00
6       Column         1     0     2                    00
7       ResultRow      2     1     0                    00
8     Next           1     5     0                    00
9     Halt           0     0     0                    00

``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.

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to