Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[x]

Thanks Ryan and Dominique. The quote “You can think of an SQLite table as 
essentially a btree covering Index by itself with the Key being the Row_ID” 
makes things a lot clearer.



Thanks to everyone for their replies and patience.



Regards



Tom








From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Dominique Devienne <ddevie...@gmail.com>
Sent: Monday, January 8, 2018 10:56:55 AM
To: SQLite mailing list
Subject: Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

On Mon, Jan 8, 2018 at 11:39 AM, x <tam118...@hotmail.com> wrote:

> However, I’m still confused. Reading this https://sqlite.org/
> queryplanner.html suggests the table is stored in RowID order. So what
> happens if I insert a record into Tbl with a lower ID than the existing 2.4
> million Ids?
>

It depends if your ID column is an alias for the ROWID special column or
not.

ROWID is the key of the B-tree I believe, so if you insert a row "in the
middle",
many pages have potentially to be rewritten to "rebalance" the B-tree.
While only the "last" page needs updating with AUTO INCREMENT or a ROWID
larger than the previous larger one.

So if ID an alias for ROWID, the insert might be much more expensive.
If ID is not, then the row is simply "appended" at the end, "cheaply".

I could be wrong of course. I'll find out very soon :). --DD
___
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

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Dominique Devienne]

On Mon, Jan 8, 2018 at 12:33 PM, R Smith  wrote:

> using any other index means a round-trip reading and hitting values in
> THAT index,

then returning and looking up the hit result in the rowid table index,

and then reading the pages(s) from it and extracting the data -

where during a table scan, all this round tripping is skipped.
>

Plus during a table-scan, you're reading the table pages "in-order", and
decoding the page only once,
for all rows on each page. While an index-scan read the index "in-order",
but the table pages "out-of-order",
since most consecutive rows (from the index) end up on different table
pages. The page-cache helps to
avoid too much IO (if big enough), but you must still decode the page for
each row to access one specific record inside it.

Sometimes the full-table-scan is the most efficient indeed. --DD

PS: But also note that a query with an order-by or group-by might still
prefer an index-scan to avoid sorting rows,
  if the index already matches the requested order, offsetting the eventual
higher cost from the index-scan.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[R Smith]

On 2018/01/08 12:39 PM, x wrote:

Thanks Cezary and Scott. I’m now a bit clearer as to what’s happening. I 
imagined the RowID as being a separate index which is the root of my confusion.


To elaborate a little - We often get people here asking "But why it 
table-scans in stead of using my nice Index?".


This stems from an often-held misconception that Indexes are God-sent 
magic to improve everything. The fact is that Indexes are costly 
mechanisms which allows fast lookup which, only AFTER a certain critical 
size and for specific circumstances, become more efficient than a scan. 
The Query Planner has to do a lot of work to figure out what those 
"critical size and specific circumstances" is for any specific query, 
and it does get real fuzzy.


I think I've heard Richard or Dan explain it as follows (if memory 
serves, someone please point out if I'm mistaken):


You can think of an SQLite table as essentially a btree covering Index 
by itself with the Key being the Row_ID (or more recently, the PK for 
WITHOUT ROWID tables). This is why the rowid (or any column serving as 
an alias to it, or the PK for WITHOUT ROWID tables) cannot have NULL 
values, but any other primary key could (in SQLite).


Being an Index by itself means that a Table-Scan is perhaps not as 
inefficient as one might think and indeed using any other index means a 
round-trip reading and hitting values in THAT index, then returning and 
looking up the hit result in the rowid table index, and then reading the 
pages(s) from it and extracting the data - where during a table scan, 
all this round tripping is skipped.


So unless any prospective candidate Index for any query offers a truly 
magnificent cost advantage, a table scan would probably be more 
efficient, and so be chosen.  This is why running ANALYZE on large 
tables is needed, because it allows the QP to better deduce whether a 
prospective Index might in fact offer such a magnificent cost reduction 
or not. Another way is hinting at the QP (Search "likelihood" in the docs).


This is why a non-rowid-alias Primary Key on a rowid table is also less 
efficient to scan than the table itself (often very non-intuitive) - or 
- why a covering index sometimes gets avoided in a JOIN when it seems to 
contain all needed data to fulfill the join obligation.


Also, often a great index is not used simply because the query planner 
does not know enough about it and its prospective cost to obtain a good 
estimate of its utility, and sometimes what feels intuitively to us as a 
great Index just isn't really. The QP is not infallible, but it is quite 
smart.



Cheers,
Ryan



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[R Smith]

[Repost: used a wrong e-mail at first, apologies if this comes through 
twice]


On 2018/01/08 12:39 PM, x wrote:

Thanks Cezary and Scott. I’m now a bit clearer as to what’s happening. I 
imagined the RowID as being a separate index which is the root of my confusion. 
It would explain this


To elaborate a little - We often get people here asking "But why it 
table-scans in stead of using my nice Index?".


This stems from an often-held misconception that Indexes are God-sent 
magic to improve everything. The fact is that Indexes are costly 
mechanisms which allows fast lookup which, only AFTER a certain critical 
size and for specific circumstances, become more efficient than a scan. 
The Query Planner has to do a lot of work to figure out what those 
"critical size and specific circumstances" is for any specific query, 
and it does get real fuzzy.


I think I've heard Richard or Dan explain it as follows (if memory 
serves, someone please point out if I'm mistaken):


You can think of an SQLite table as essentially a btree covering Index 
by itself with the Key being the Row_ID (or more recently, the PK for 
WITHOUT ROWID tables). This is why the rowid (or any column serving as 
an alias to it, or the PK for WITHOUT ROWID tables) cannot have NULL 
values, but any other primary key could (in SQLite).


Being an Index by itself means that a Table-Scan is perhaps not as 
inefficient as one might think and indeed using any other index means a 
round-trip reading and hitting values in THAT index, then returning and 
looking up the hit result in the rowid table index, and then reading the 
pages(s) from it and extracting the data - where during a table scan, 
all this round tripping is skipped.


So unless any prospective candidate Index for any query offers a truly 
magnificent cost advantage, a table scan would probably be more 
efficient, and so be chosen.  This is why running ANALYZE on large 
tables is needed, because it allows the QP to better deduce whether a 
prospective Index might in fact offer such a magnificent cost reduction 
or not. Another way is hinting at the QP (Search "likelihood" in the docs).


This is why a non-rowid-alias Primary Key on a rowid table is also less 
efficient to scan than the table itself (often very non-intuitive) - or 
- why a covering index sometimes gets avoided in a JOIN when it seems to 
contain all needed data to fulfill the join obligation.


Also, often a great index is not used simply because the query planner 
does not know enough about it and its prospective cost to obtain a good 
estimate of its utility, and sometimes what feels intuitively to us as a 
great Index just isn't really. The QP is not infallible, but it is quite 
smart.



Cheers,
Ryan



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Dominique Devienne]

On Mon, Jan 8, 2018 at 11:39 AM, x  wrote:

> However, I’m still confused. Reading this https://sqlite.org/
> queryplanner.html suggests the table is stored in RowID order. So what
> happens if I insert a record into Tbl with a lower ID than the existing 2.4
> million Ids?
>

It depends if your ID column is an alias for the ROWID special column or
not.

ROWID is the key of the B-tree I believe, so if you insert a row "in the
middle",
many pages have potentially to be rewritten to "rebalance" the B-tree.
While only the "last" page needs updating with AUTO INCREMENT or a ROWID
larger than the previous larger one.

So if ID an alias for ROWID, the insert might be much more expensive.
If ID is not, then the row is simply "appended" at the end, "cheaply".

I could be wrong of course. I'll find out very soon :). --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[x]

Thanks Cezary and Scott. I’m now a bit clearer as to what’s happening. I 
imagined the RowID as being a separate index which is the root of my confusion. 
It would explain this



sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL ORDER BY ID;

0|0|0|SCAN TABLE TBL



contrasted with



sqlite> EXPLAIN QUERY PLAN SELECT X FROM TBL ORDER BY X;

0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX



However, I’m still confused. Reading this https://sqlite.org/queryplanner.html 
suggests the table is stored in RowID order. So what happens if I insert a 
record into Tbl with a lower ID than the existing 2.4 million Ids?






From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Scott Robison <sc...@casaderobison.com>
Sent: Sunday, January 7, 2018 7:30:12 PM
To: SQLite mailing list
Subject: Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

Integer primary key is by definition not null, so looking for a null value
on an index can't work. I guess there exists an optimization opportunity to
just return an emotional set, though it seems easier to not specify an
impossible condition.

As to why it does a table scan, the primary key isn't a separate index. The
rowid primary key is part of the table itself.

On Jan 7, 2018 11:22 AM, "Luuk" <luu...@gmail.com> wrote:

> On 07-01-18 19:09, x wrote:
> >> Because reading the whole record (all 3 fields) is more expensive than
> >> just reading the index which has all the info you need to give a correct
> >> answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'
> > Yes, but the covering index has 2 fields (X & ID). The pk has only 1
> field (ID) so why not use that, particularly in the case where ‘ORDER BY
> ID’ was included in the query?
> >
> >
> sorry, i do not know
> ___
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Cezary H. Noweta]

Hello,

To summarize:

On 2018-01-07 19:09, x wrote:

Because reading the whole record (all 3 fields) is more expensive than
just reading the index which has all the info you need to give a correct
answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'


Yes, but the covering index has 2 fields (X & ID). The pk has only 1 field (ID) 
so why not use that, particularly in the case where ‘ORDER BY ID’ was included in 
the query?


OK - your creation statement causes that ``ID'' is an alias for 
``rowid''. Why PK is not used, was mentioned by me:


On 2018-01-06 14:01, Cezary H. Noweta 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.


and Scott:

On 2018-01-07 20:30, Scott Robison wrote:

Integer primary key is by definition not null, so looking for a null value
on an index can't work.


Look at ``where.c:whereLoopAddBtreeIndex()'':

if( (eOp==WO_ISNULL || (pTerm->wtFlags_VNULL)!=0)
 && indexColumnNotNull(pProbe, saved_nEq)
){
  continue; /* ignore IS [NOT] NULL constraints on NOT NULL columns */
}


Even if you used ``ID INT PRIMARY...'' instead of ``ID INTEGER 
PRIMARY...'' (which did not aliased ``rowid'' to ``ID'') results would 
be the same, because ``ID'' is not NULL.


Now we are considering a full scan. As Luuk wrote:

On 2018-01-07 18:55, Luuk wrote:

Because reading the whole record (all 3 fields) is more expensive than
just reading the index which has all the info you need to give a correct
answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'


Calculating a cost of a DB's searching is a bit complicated and cannot 
be described in two words. You could start at a comment of 
``where.c:whereLoopAddBtree()''. When your ratio of a length of an index 
element to a length of a table row has been lowered, then index ``XXX'' 
is chosen as the most effective solution. (``rowid'' pseudo-index' 
entries are considered as being equal to a whole row of a table).


However this index is used for a full scan -- not for a binary/log_2n 
searching for values: ``Yahoo! My query is using an index so it 
achieved a speed of light''. If it was true, then certainly ``XXX'' 
would be used earlier.


If you want to enforce some index to be used you will have to append 
``INDEXED BY'' clause, however:


1. Choosing a bad index will result in no index at all.

2. As Scott noticed:

On 2018-01-07 20:30, Scott Robison wrote:

As to why it does a table scan, the primary key isn't a separate index. The
rowid primary key is part of the table itself.


For the purpose of ``SELECT'' there is a pseudo-index created for 
``rowid'' -- it is impossible to enforce that pseudo-index. Unless you 
use ``INT'' instead of ``INTEGER'' in your ``CREATE TABLE'' statement 
(which will leave ``rowid'', and ``ID'' will not be alias for ``rowid'') 
-- such primary index will be named ,,sqlite_autoindex__'' 
-- you will have to guess  (or look into a db-file, or -- at best -- 
SELECT from ``sqlite_master'').


Primary key ``rowid'' (or alias) is not indexed, only ``primary keys'' 
other then ``rowid'' are. In fact it is impossible to have NULL 
``rowid'' (or alias) in destroyed db -- due to a fact that it has NOT 
NULL, int64 (variable length) format -- there is no place for ``I'm a 
NULL'' flag. Non-rowid, NOT NULL, primary keys with NULL value could be 
occurring eventually.


I hope, above will help and dispel any doubts.

-- best regards

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

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Scott Robison]

Integer primary key is by definition not null, so looking for a null value
on an index can't work. I guess there exists an optimization opportunity to
just return an emotional set, though it seems easier to not specify an
impossible condition.

As to why it does a table scan, the primary key isn't a separate index. The
rowid primary key is part of the table itself.

On Jan 7, 2018 11:22 AM, "Luuk"  wrote:

> On 07-01-18 19:09, x wrote:
> >> Because reading the whole record (all 3 fields) is more expensive than
> >> just reading the index which has all the info you need to give a correct
> >> answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'
> > Yes, but the covering index has 2 fields (X & ID). The pk has only 1
> field (ID) so why not use that, particularly in the case where ‘ORDER BY
> ID’ was included in the query?
> >
> >
> sorry, i do not know
> ___
> 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

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Luuk]

On 07-01-18 19:09, x wrote:
>> Because reading the whole record (all 3 fields) is more expensive than
>> just reading the index which has all the info you need to give a correct
>> answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'
> Yes, but the covering index has 2 fields (X & ID). The pk has only 1 field 
> (ID) so why not use that, particularly in the case where ‘ORDER BY ID’ was 
> included in the query?
>
>
sorry, i do not know
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[x]

>Because reading the whole record (all 3 fields) is more expensive than
>just reading the index which has all the info you need to give a correct
>answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'

Yes, but the covering index has 2 fields (X & ID). The pk has only 1 field (ID) 
so why not use that, particularly in the case where ‘ORDER BY ID’ was included 
in the query?


From: Luuk<mailto:luu...@gmail.com>
Sent: 07 January 2018 18:05
To: 
sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

On 07-01-18 18:49, x wrote:
> 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.
>

Because reading the whole record (all 3 fields) is more expensive than
just reading the index which has all the info you need to give a correct
answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'

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

___
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

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Luuk]

On 07-01-18 18:49, x wrote:
> 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.
>

Because reading the whole record (all 3 fields) is more expensive than
just reading the index which has all the info you need to give a correct
answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'

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

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[x]

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

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Luuk]

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

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[x]

>>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
>
>
>
> addropcode   p1p2p3   p4p5comment
>
> 0  Init  0  8  0  00   Start at 8
>
> 1  Goto   0  7  0 00
>
> 2  OpenRead  1  109 0  k(2,,)   00root=109 iDb=0; tID
>
> 3  Rewind   1  7  1  0  00
>
> 4  IdxRowid1  1  0 00r[1]=rowid
>
> 5  ResultRow  1  1  0 00output=r[1]
>
> 6  Next1  4  0 01
>
> 7  Halt 0  0  0  00
>
> 8  Transaction0  0  392 0  01usesStmtJournal=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

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Keith Medcalf]

>Neither will return any values since a primary key can not be NULL.

Untrue.  
The RowID alias "integer primary key" cannot be NULL  
However components of primary key(...) can be null (ie, that are not aliases 
for the RowID) and are not the PRIMARY KEY of a without rowid table.

" According to the SQL standard, PRIMARY KEY should always imply NOT NULL. 
Unfortunately, due to a bug in some early versions, this is not the case in 
SQLite. Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT 
ROWID table or the column is declared NOT NULL, SQLite allows NULL values in a 
PRIMARY KEY column. SQLite could be fixed to conform to the standard, but doing 
so might break legacy applications. Hence, it has been decided to merely 
document the fact that SQLite allowing NULLs in most PRIMARY KEY columns. "

Under SQL Data Constraints at https://sqlite.org/lang_createtable.html

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Peter Da Silva]

These are different requests in SQL.

"ID = NULL" is comparing the ID to "NULL". Comparing any value to "NULL" fails. 
This is equivalent to

SELECT ID FROM Tbl WHERE FALSE;

A smarter query planner would run it in zero ms. :)

"ID IS NULL" is checking if the value in ID is null.

Neither will return any values since a primary key can not be NULL.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Cezary H. Noweta]

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



addropcode   p1p2p3   p4p5comment

0  Init  0  8  0  00   Start at 8

1  Goto   0  7  0 00

2  OpenRead  1  109 0  k(2,,)   00root=109 iDb=0; tID

3  Rewind   1  7  1  0  00

4  IdxRowid1  1  0 00r[1]=rowid

5  ResultRow  1  1  0 00output=r[1]

6  Next1  4  0 01

7  Halt 0  0  0  00

8  Transaction0  0  392 0  01usesStmtJournal=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

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[x]

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



addropcode   p1p2p3   p4p5comment

0  Init  0  8  0  00   Start at 8

1  Goto   0  7  0 00

2  OpenRead  1  109 0  k(2,,)   00root=109 iDb=0; tID

3  Rewind   1  7  1  0  00

4  IdxRowid1  1  0 00r[1]=rowid

5  ResultRow  1  1  0 00output=r[1]

6  Next1  4  0 01

7  Halt 0  0  0  00

8  Transaction0  0  392 0  01usesStmtJournal=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 000
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 200
7   ResultRow  2 1 000
8 Next   1 5 000
9 Halt   0 0 000

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

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Cezary H. Noweta]

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 000
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 200
7   ResultRow  2 1 000
8 Next   1 5 000
9 Halt   0 0 000

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

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[x]

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


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


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Luuk]

42
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Luuk]

On 06-01-18 10:44, x wrote:
> Thanks for the replies.
>
> While I’m now on board as to what a NULL is I’m still a bit puzzled by the 
> query planner.
>
> Explain query plan select ID from Tbl where ID is null order by ID;
>
> returns
>
> SCAN TABLE Tbl USING COVERING INDEX ...
>
>
I do see different things:
sqlite> .version
SQLite 3.21.0 2017-10-24 18:55:49
1a584e499906b5c87ec7d43d4abce641fdf017c42125b083109bc77c4de4alt2
sqlite> .schema tbl
CREATE TABLE tbl (id integer primary key not null);
sqlite> select count(*) from tbl;
count(*)
8388608
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
sqlite>

> 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...
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Eric]

On Sat, 6 Jan 2018 09:53:46 +0100, Luuk  wrote:
> On 06-01-18 00:49, Simon Slavin wrote:
>>
>> To expand on this, in SQL NULL has a special meaning.  
> 
> This should read:
> NULL has a special meaning.
> and not:
> in SQL NULL has a special meaning.
> 
> Because the use of NULL is not 'reserverd' for SQL, and in SQL it is not
> more special than in any other environment.

I don't understand what you mean. NULL has a special
meaning in SQL (Structured Query Language), and that is what we are
talking about.

Eric
-- 
ms fnd in a lbry
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[x]

Thanks for the replies.

While I’m now on board as to what a NULL is I’m still a bit puzzled by the 
query planner.

Explain query plan select ID from Tbl where ID is null order by ID;

returns

SCAN TABLE Tbl USING COVERING INDEX ...

It doesn’t even use the ID pk despite the fact it’s the requested order by??


> What has you checking an integer primary key for null anyway?

David, to test something I needed a query that returned 0 rows and I modified 
the query I was already working on rather than type out a new select.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Luuk]

On 06-01-18 00:49, Simon Slavin wrote:
>
> To expand on this, in SQL NULL has a special meaning.  

This should read:
NULL has a special meaning.
and not:
in SQL NULL has a special meaning.

Because the use of NULL is not 'reserverd' for SQL, and in SQL it is not
more special than in any other environment.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Cezary H. Noweta]

Hello,

On 2018-01-05 21:56, David Raymond wrote:

sqlite> explain select id from tbl where id = null;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 8 000  Start at 8
1 OpenRead   0 3 0 0  00  root=3 iDb=0; tbl
2 Explain0 0 0 SEARCH TABLE tbl USING INTEGER PRIMARY 
KEY (rowid=?)  00
3 Null   0 1 000  r[1]=NULL
4 SeekRowid  0 7 100  intkey=r[1]; pk
5 Copy   1 2 000  r[2]=r[1]
6 ResultRow  2 1 000  output=r[2]
7 Halt   0 0 000
8 Transaction0 0 2 0  01  usesStmtJournal=0
9 Goto   0 1 000
Run Time: real 0.000 user 0.00 sys 0.00


??? My 3.21 checks if NULL is NULL and exits:

2 Null   0 1
3 IsNull 1 9
...
9 Halt   0 0

-- best regards

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

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Simon Slavin]

On 5 Jan 2018, at 9:03pm, Doug Currie  wrote:

> NULL is not equal to NULL, though NULL is NULL.
> 
> sqlite> select NULL IS NULL;
> 
> 1
> 
> sqlite> select NULL = NULL;
> 
> 
> sqlite>

To expand on this, in SQL NULL has a special meaning.  It means "value unknown 
or missing".  It turns up naturally in some places, for example, in JOINs where 
the joined row doesn’t exist.  Don’t think of NULL as a value.  Think of it as 
a flag.

Because of this, testing for NULL is unexpectedly complicated, as shown by the 
above.  Two values might both be unknown, but this doesn’t automatically mean 
that they have the same value.  Yet an unknown value is indeed unknown, even if 
we don’t know what it is.  That’s why you get the results Doug posted above.

Many examples I see which store NULL as a value in the database don’t really 
mean NULL, they mean 0 or the empty string, or an empty list, and changing the 
database to store a more appropriate value lets the programmer strip many lines 
of complicated logic out of their software.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Doug Currie]

Cezary is correct,

NULL is not equal to NULL, though NULL is NULL.

sqlite> select NULL IS NULL;

1

sqlite> select NULL = NULL;


sqlite>


e
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Cezary H. Noweta]

Hwllo,

On 2018-01-05 21:19, x wrote:

Tbl has 2.4 million records and has an INTEGER PRIMARY KEY ID which is NOT NULL.

SELECT ID FROM Tbl where ID = NULL


It has no sense as ``='' returns NULL when one of operands is NULL. NULL 
evaluates to FALSE. Above query can be described as SELECT NOTHING.



SELECT ID FROM Tbl WHERE ID IS NULL



takes around 350 milliseconds to run and the explain query plan shows it uses 
some covering index.


SQLite is looking for NULL values of ID.

Please, please, do not require to optimize such query due to the fact 
that your field does not contain NULLs. :-)



I’m sure I’ve read something before about this but can’t find it in the 
documentation?


STH IS STH allows NULLs and returns 1 if both are NULLs, 0 otherwise. 
http://sqlite.org/lang_expr.html#isisnot


-- best regards

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

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[David Raymond]

I  it's treating "is" as an operator on the value.

So with the = version, it knows it can use the index to zip to where the nulls 
would be and ends pretty quick when it doesn't find any.

With the IS, it thinks it has to check every value because indexes are sorted 
on =, <, > etc, and not on "is"

This is just my guess anyway. What has you checking an integer primary key for 
null anyway?


SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table tbl (id integer primary key);

sqlite> explain query plan select id from tbl where id = null;
selectid|order|from|detail
0|0|0|SEARCH TABLE tbl USING INTEGER PRIMARY KEY (rowid=?)

sqlite> explain query plan select id from tbl where id is null;
selectid|order|from|detail
0|0|0|SCAN TABLE tbl

sqlite> explain select id from tbl where id = null;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 8 000  Start at 8
1 OpenRead   0 3 0 0  00  root=3 iDb=0; tbl
2 Explain0 0 0 SEARCH TABLE tbl USING INTEGER PRIMARY 
KEY (rowid=?)  00
3 Null   0 1 000  r[1]=NULL
4 SeekRowid  0 7 100  intkey=r[1]; pk
5 Copy   1 2 000  r[2]=r[1]
6 ResultRow  2 1 000  output=r[2]
7 Halt   0 0 000
8 Transaction0 0 2 0  01  usesStmtJournal=0
9 Goto   0 1 000
Run Time: real 0.000 user 0.00 sys 0.00

sqlite> explain select id from tbl where id is null;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 10000  Start at 10
1 OpenRead   0 3 0 0  00  root=3 iDb=0; tbl
2 Explain0 0 0 SCAN TABLE tbl  00
3 Rewind 0 9 000
4   Rowid  0 1 000  r[1]=rowid
5   NotNull1 8 000  if r[1]!=NULL goto 8
6   Copy   1 2 000  r[2]=r[1]
7   ResultRow  2 1 000  output=r[2]
8 Next   0 4 001
9 Halt   0 0 000
10Transaction0 0 2 0  01  usesStmtJournal=0
11Goto   0 1 000


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of x
Sent: Friday, January 05, 2018 3:20 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

Tbl has 2.4 million records and has an INTEGER PRIMARY KEY ID which is NOT NULL.

SELECT ID FROM Tbl where ID = NULL

takes only a few milliseconds to run and the explain query plan shows it uses 
the primary key.

SELECT ID FROM Tbl WHERE ID IS NULL

takes around 350 milliseconds to run and the explain query plan shows it uses 
some covering index.


I’m sure I’ve read something before about this but can’t find it in the 
documentation?

___
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

[sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[x]

Tbl has 2.4 million records and has an INTEGER PRIMARY KEY ID which is NOT NULL.

SELECT ID FROM Tbl where ID = NULL

takes only a few milliseconds to run and the explain query plan shows it uses 
the primary key.

SELECT ID FROM Tbl WHERE ID IS NULL

takes around 350 milliseconds to run and the explain query plan shows it uses 
some covering index.


I’m sure I’ve read something before about this but can’t find it in the 
documentation?

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users