difference between 'ID IS NULL' and 'ID = NULL'

classic Classic list List threaded Threaded
37 messages Options
12
Reply | Threaded
Open this post in threaded view
|

difference between 'ID IS NULL' and 'ID = NULL'

curmudgeon
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

David Raymond
I <think> 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         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     8     0                    00  Start at 8
1     OpenRead       0     3     0     0              00  root=3 iDb=0; tbl
2     Explain        0     0     0     SEARCH TABLE tbl USING INTEGER PRIMARY KEY (rowid=?)  00
3     Null           0     1     0                    00  r[1]=NULL
4     SeekRowid      0     7     1                    00  intkey=r[1]; pk
5     Copy           1     2     0                    00  r[2]=r[1]
6     ResultRow      2     1     0                    00  output=r[2]
7     Halt           0     0     0                    00
8     Transaction    0     0     2     0              01  usesStmtJournal=0
9     Goto           0     1     0                    00
Run Time: real 0.000 user 0.000000 sys 0.000000

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


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of x
Sent: Friday, January 05, 2018 3:20 PM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

Cezary H. Noweta
In reply to this post by curmudgeon
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

Doug Currie-2
In reply to this post by curmudgeon
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

Simon Slavin-3
On 5 Jan 2018, at 9:03pm, Doug Currie <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

Cezary H. Noweta
In reply to this post by David Raymond
Hello,

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

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

??? 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

Luuk
In reply to this post by Simon Slavin-3
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

curmudgeon
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

Eric-2
In reply to this post by Luuk
On Sat, 6 Jan 2018 09:53:46 +0100, Luuk <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

Luuk
In reply to this post by curmudgeon
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

Luuk
In reply to this post by Eric-2
42
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

curmudgeon
In reply to this post 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

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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     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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

curmudgeon
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 <[hidden email]> on behalf of Cezary H. Noweta <[hidden email]>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
>
>
>
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

Peter da Silva
In reply to this post by curmudgeon
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

curmudgeon
In reply to this post by Cezary H. Noweta
>>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 <[hidden email]> on behalf of Cezary H. Noweta <[hidden email]>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

curmudgeon
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 <[hidden email]> on behalf of Luuk <[hidden email]>
Sent: Sunday, January 7, 2018 1:35:43 PM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12