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

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



Yeah, I would get that result as well if I had no secondary indexes on Tbl. If 
either you or Luuk add a secondary index XXX to your versions of Tbl you’ll get 
the same result I’m getting and maybe then we’ll be on the same page regarding 
ordering.



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 3:09:59 PM
To: SQLite mailing list
Subject: Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

Hello,

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;
selectid|order|from|detail
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
>
>
>
> SCAN TABLE Tbl USING COVERING INDEX XXX

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