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 <[email protected]> on behalf of Cezary H. Noweta <[email protected]> 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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
