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

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

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

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 -

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

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

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

[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

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

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

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

isting 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 'I

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

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 &

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

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

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

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

o 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 'I

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)

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

___ 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:0

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,

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

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

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

>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

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

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

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

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

.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

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

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

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

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

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'

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[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