Luuk and Cezary, my apologies. Looking at my opening post(s) I didn’t make it clear that Tbl had numerous secondary indexes attached to it. The table has 2.4 million records and 13 columns. There is a non-unique index on one of the other columns (x integer) which is the one appearing in my explain query plan. The following illustrates the problem. I’ve no idea why adding the 3rd column is necessary to replicate it.
sqlite> CREATE TABLE TBL (ID INTEGER PRIMARY KEY NOT NULL); sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL; 0|0|0|SCAN TABLE TBL sqlite> ALTER TABLE TBL ADD COLUMN X INTEGER; sqlite> CREATE INDEX XXX ON TBL(X); sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL; 0|0|0|SCAN TABLE TBL sqlite> ALTER TABLE TBL ADD COLUMN Y INTEGER; sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL; 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE 0; 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL ORDER BY ID; 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX sqlite> ________________________________ From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Luuk <luu...@gmail.com> Sent: Sunday, January 7, 2018 1:35:43 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL' On 06-01-18 19:00, 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 >> 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. > > > I still do not knwo what you are doing... multiple indexes define, and i stillsee 'SCAN TABLE ...' can you post the output of your table definition? sqlite> .schema tbl CREATE TABLE tbl (id integer primary key not null); CREATE INDEX idx2 on tbl(id); CREATE INDEX idx3 on tbl(id DESC); sqlite> explain query plan select id from tbl where id is null; 0|0|0|SCAN TABLE tbl sqlite> _______________________________________________ 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
