>>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 <[email protected]> on behalf of Cezary H. Noweta <[email protected]> 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 [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
