[sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?

[Shane Dev]

Hello,

The view VRAND below generates a series of 3 randomly chosen integers -

CREATE VIEW vrand as with r(num, rand) as (
select 1, cast(round(abs(random())/9223372036854775808) as int)
union all
select num+1, cast(round(abs(random())/9223372036854775808) as int) from r)
select num from r where rand=1 limit 3;

sqlite> select * from vrand;
num
1
2
4

sqlite> select count(*) from vrand;
count(*)
3

Now I would like to add a "running number" column with a result set like -

running_num, num
1, 1
2, 2
3, 4

However, the follow statement seems to trigger an infinite loop -

select (select count(*) from vrand where num <= v.num), num from vrand as v;

How can a running number column be added to this view?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The performance of indexed select

[Nick]

Thank you Keith for your useful advice. I am considering to organize the
columns based on BCNF.

I guess that table t3 is needed to remove functional dependency, which means
I should use table t2 and t3 instead of one table t2 with 4 columns a-d. Is
that right?

I am not familiar with the concept BCNF, and I want to make sure that if it
is recommended to create my tables in the way you wrote.

Thanks



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
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] The performance of indexed select

[Dinu]

... and the downside that it's just linear overhead for i.e. an unique index,
it works best for indexes with low cardinality... win some, lose some :)



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The performance of indexed select

[Dinu]

What I do notice reading https://www.sqlite.org/fileformat.html (if I get it
right) is that index lines are in the form (for an index on a,b,c ie):

   
   
   

Whereas they could be represented as:

   [ , ,  ](3)

whith [pk_list] being a subtree; reverse lookup from table record to index
record would be arguably as fast if not faster. So this would have the
advantage of more compact indexes (less data), having an index line count
(no prefix so there is always just 1 update involved), with the downside of
the complexity of an added level of indirection.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieving constraint name

[Igor Korot]

Hi, Cezary et al,

On Mon, Dec 11, 2017 at 5:48 PM, Cezary H. Noweta  wrote:
> Hello,
>
> On 2017-12-11 04:29, Igor Korot wrote:
>>
>> Thank you, but I need to keep the official SQLite code.
>
> Anyway, for the people who are interested in foreign key names:
> http://sqlite.chncc.eu/fknames/. Certainly, all tests expecting original
> output of ``PRAGMA foreign_key_list'' will fail.

It would be nice if the extra column for the fk name will appear last in the
output. I understand that that's how most DBMSs are work, but  that
way the backward
compatibility can be preserved.

Then maybe it could be considered to be included in the main tree by
simply applying the patch.

Thank 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] Tip of Trunk does not compile under 64-bit MingW compiler

[Richard Hipp]

Please try again with the latest trunk of SQLite
(https://www.sqlite.org/src/timeline?c=2018-01-07+21:58:17 or later)
and let me know whether or not your a still having problems.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_name with alias

[Bart Smissaert]

OK, I can see your point and I am sure you are right.
All this has to do with the question (discussed not long ago) how one
should know how the output from a statement should be handled by
an application receiving the data. In my case that application is Excel.
Take for example an integer. Excel need to know if this integer should
be formatted as a plain integer or as an Excel date. When the user moves
data to SQLite (from the sheet or from eg a text file) he/she will tell how
this data should be stored. In this case the data type will be INTEGER or
INT_DATE. This information will be stored in SQLite_master. The question
is now how to match up the columns of the output with these data types in
SQLite_master. I have a simple system for this with sqlite3_column_type
and sqlite3_column_decltype etc. but this is just not fool proof. It looks
I will need to think up some more elaborate system. One option is that the
user
just tells (along with the statement) what the output formatting should be,
but that would be cumbersome.

RBS

On Sun, Jan 7, 2018 at 9:10 PM, R Smith  wrote:

>
> On 2018/01/07 10:11 PM, Bart Smissaert wrote:
>
>> Sure, in that case there can be no sensible column name.
>> In the great majority of cases though the select field will be of a single
>> table column, with or without
>> an expression. In those cases it will be helpful to get the non alias
>> column name.
>>
>
> To repeat an answer by Richard given for a recent other question (if a
> little paraphrased):
>
> How much extra code, resources and memory are you willing to spend for ALL
> future queries so that you can know the sometimes available original column
> used in an alias?
>
> I would prefer that answer to be "Zero".
>
> Bart:
>
> "One would think that if sqlite3_column_name can get the alias name then
> somehow it must
> be possible to get the real column name."
>
> One would be wrong if one would think that. Inside the code/engine there
> are no column names, only pointers, (in fact I think the engine has to make
> special memory space for storing the output names - it is this memory that
> gets interrogated when you call sqlite3_column_name()),  the names only
> exist as a method for humans to state the intent of the question - it is
> forgotten at earliest convenience if not directly intended for output, and
> those that do get added to output has to be collected/deduced at a non-zero
> resource cost already. (At least this is my current understanding, I might
> have it wrong).
>
> Btw. - I am quite sure this holds for all DB engines, but if there is an
> engine out there that do let you trace back, I'd be interested to know.
>
> Also, the assumption about the "vast majority of cases" is simply wrong.
> The vast majority of returned column names in queries are not aliases,
> indeed aliases are mostly used when the column value is a result of a
> function or combination of columns (such as Simon's reply suggested) where
> you need a good resulting reference name in the output. Are you using
> aliases for columns where they are not needed?
>
> Further to this, there is nothing wrong with copying the name in the
> alias, such as: SELECT MAX(ColA) AS ColA FROM 
>
> Lastly, even if we involve zero column joining or functions, plain
> aliasing is a problem by itself. Consider this next query, what do you feel
> is the true original name of the output column sq1?
> CREATE VIEW v1 AS SELECT c1 AS vc1 FROM (SELECT 'X' AS c1);
> SELECT sq1 FROM (SELECT vc1 AS sq1 FROM v1);
> sq1
> -
>   X
>
> HTH, and I hope this adequately illustrates why your request is not
> possible currently, not trivial to implement and also not widely requested.
> Cheers!
> Ryan
>
>
>
>
>
>
> ___
> 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] sqlite3_column_name with alias

[R Smith]

On 2018/01/07 10:11 PM, Bart Smissaert wrote:

Sure, in that case there can be no sensible column name.
In the great majority of cases though the select field will be of a single
table column, with or without
an expression. In those cases it will be helpful to get the non alias
column name.


To repeat an answer by Richard given for a recent other question (if a 
little paraphrased):


How much extra code, resources and memory are you willing to spend for 
ALL future queries so that you can know the sometimes available original 
column used in an alias?


I would prefer that answer to be "Zero".

Bart:

"One would think that if sqlite3_column_name can get the alias name then
somehow it must
be possible to get the real column name."

One would be wrong if one would think that. Inside the code/engine there 
are no column names, only pointers, (in fact I think the engine has to 
make special memory space for storing the output names - it is this 
memory that gets interrogated when you call sqlite3_column_name()),  the 
names only exist as a method for humans to state the intent of the 
question - it is forgotten at earliest convenience if not directly 
intended for output, and those that do get added to output has to be 
collected/deduced at a non-zero resource cost already. (At least this is 
my current understanding, I might have it wrong).


Btw. - I am quite sure this holds for all DB engines, but if there is an 
engine out there that do let you trace back, I'd be interested to know.


Also, the assumption about the "vast majority of cases" is simply wrong. 
The vast majority of returned column names in queries are not aliases, 
indeed aliases are mostly used when the column value is a result of a 
function or combination of columns (such as Simon's reply suggested) 
where you need a good resulting reference name in the output. Are you 
using aliases for columns where they are not needed?


Further to this, there is nothing wrong with copying the name in the 
alias, such as: SELECT MAX(ColA) AS ColA FROM 


Lastly, even if we involve zero column joining or functions, plain 
aliasing is a problem by itself. Consider this next query, what do you 
feel is the true original name of the output column sq1?

CREATE VIEW v1 AS SELECT c1 AS vc1 FROM (SELECT 'X' AS c1);
SELECT sq1 FROM (SELECT vc1 AS sq1 FROM v1);
sq1
-
  X

HTH, and I hope this adequately illustrates why your request is not 
possible currently, not trivial to implement and also not widely requested.

Cheers!
Ryan





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

Re: [sqlite] sqlite3_column_name with alias

[Bart Smissaert]

Sure, in that case there can be no sensible column name.
In the great majority of cases though the select field will be of a single
table column, with or without
an expression. In those cases it will be helpful to get the non alias
column name.

RBS



On Sun, Jan 7, 2018 at 7:59 PM, Simon Slavin  wrote:

>
>
> On 7 Jan 2018, at 6:16pm, Bart Smissaert  wrote:
>
> > Would it be possible somehow to get the column full name if there is a
> > column alias?
> >
> > For example if we have:
> >
> > select max(ID) as MaxID from table1
> >
> > then I need to get max(ID) from the statement pointer.
> >
> > One would think that if sqlite3_column_name can get the alias name then
> > somehow it must
> > be possible to get the real column name.
>
> Suppose you had this
>
> SELECT (a + b) AS theSum FROM myTable
>
> What would the "real column name" be ?
>
> Simon.
> ___
> 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] sqlite3_column_name with alias

[Simon Slavin]

On 7 Jan 2018, at 6:16pm, Bart Smissaert  wrote:

> Would it be possible somehow to get the column full name if there is a
> column alias?
> 
> For example if we have:
> 
> select max(ID) as MaxID from table1
> 
> then I need to get max(ID) from the statement pointer.
> 
> One would think that if sqlite3_column_name can get the alias name then
> somehow it must
> be possible to get the real column name.

Suppose you had this

SELECT (a + b) AS theSum FROM myTable

What would the "real column name" be ?

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

Re: [sqlite] Efficient query to count number of leaves in a DAG.

[Shane Dev]

Hi David,

I started from scratch with a new database and confirmed your findings -
v_count_leaves_new is actually faster than  leafCounts.

My error in the original database was neglecting to specify type of the
columns in the EDGES table -

CREATE TABLE edges(parent not null references nodes, child not null
references nodes, primary key(parent, child)) without rowid;

I had assumed the EDGES columns would inherit the affinity of the
referenced table key (NODES.id). However after correcting the table
definition to

CREATE TABLE edges(parent *int* not null references nodes, child *int* not
null references nodes, primary key(parent, child)) without rowid;

queries over 1 nodes and edges dropped from 45 to 0.1 sec. A valuable
lesson for me!

On 6 January 2018 at 00:28, David Raymond  wrote:

> Something is seriously funky here. I'm getting the opposite, where your
> query appears to be going faster than mine. I used your queries there to
> populate nodes and edges, based on 1,000,000 nodes. I even added in the
> extra index which turns out isn't used anyway. With it all in memory my
> version is taking 59 seconds, whereas your _new version is taking 28
> seconds and the old version is only 34. So apparently I should be taking
> query advice from you.
>
> If I change my union into a union all it goes down to 31 seconds, so
> closer to yours.
> If I change your union all into a union the time jumps to 156 seconds.
> I think I was thinking of a graph with possible loops or multiple paths to
> get from A to B, which is why I went with the union.
>
> So my next question is: what SQLite version are you using, and what
> hardware are you on?
>
> Are you query plans looking like what I'm seeing here?
>
> sqlite> select * from v_count_leaves_new where top = 777;
> --EQP-- 3,0,0,SCAN TABLE nodes
> --EQP-- 4,0,1,SCAN TABLE r
> --EQP-- 4,1,0,SEARCH TABLE edges AS e USING COVERING INDEX
> sqlite_autoindex_edges_1 (parent=?)
> --EQP-- 2,0,0,COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)
> --EQP-- 1,0,0,SCAN SUBQUERY 2
> --EQP-- 0,0,0,USING INDEX sqlite_autoindex_edges_1 FOR IN-OPERATOR
> --EQP-- 0,0,0,SCAN SUBQUERY 1
> top|count(*)
> 777|314
> Run Time: real 28.502 user 28.454582 sys 0.00
>
> --now with union all
> sqlite> select * from leafCounts2 where parent = 777;
> --EQP-- 3,0,0,SCAN TABLE edges
> --EQP-- 4,0,0,SCAN TABLE paths
> --EQP-- 4,1,1,SEARCH TABLE edges USING COVERING INDEX
> sqlite_autoindex_edges_1 (parent=?)
> --EQP-- 2,0,0,COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)
> --EQP-- 1,0,0,SCAN SUBQUERY 2
> --EQP-- 1,0,0,EXECUTE LIST SUBQUERY 5
> --EQP-- 5,0,0,SCAN TABLE nodes
> --EQP-- 5,1,1,SEARCH TABLE edges USING COVERING INDEX
> sqlite_autoindex_edges_1 (parent=?)
> --EQP-- 0,0,0,SCAN SUBQUERY 1
> parent|leafCount
> 777|314
> Run Time: real 31.590 user 31.434202 sys 0.00
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Shane Dev
> Sent: Friday, January 05, 2018 4:57 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Efficient query to count number of leaves in a DAG.
>
> Hi David,
>
> According to https://sqlite.org/lang_with.html, "Optimization note: ...if
> the example had used UNION instead of UNION ALL, then SQLite would have had
> to keep around all previously generated content in order to check for
> duplicates. For this reason, programmers should strive to use UNION ALL
> instead of UNION when feasible."
>
> Despite that, your RCTE with UNION is much faster than mine.
>
> sqlite> select count(*) from nodes;
> count(*)
> 1
> sqlite> select count(*) from edges;
> count(*)
> 9990
>
> Here is how create my test data -
>
> sqlite> .sch v_generate_nodes
> -- Generates an infinite series of x, 'nodex' records where x = 1, 2, 3 ...
> CREATE VIEW v_generate_nodes as with recursive rcte(id, description) as
> (select 1, 'node1' union all select id+1, 'node'||(id+1) from rcte) select
> * from rcte;
> sqlite> insert into nodes select from v_generate_nodes limit 1;
>
> sqlite> .sch v_generate_edges
> -- Randomly generates edges between entries in the nodes table.
> ---Assumption : node ids are 1, 2, 3...n without gaps
> -- Each node will have 0 or 1 parents and 0, 1, 2, ... children
> CREATE VIEW v_generate_edges as with rcte(parent, child) as (select
> cast(abs(random())/9223372036854775808 as integer), 1 union all select
> cast(abs(random())/9223372036854775808*(child+1) as integer), child+1 from
> rcte where child <= (select count(*) from nodes) limit (select count(*)
> from nodes)) select * from rcte where parent>0;
> sqlite> insert into edges select * from v_generate_edges;
>
>
>
> On 5 January 2018 at 18:32, David Raymond 
> wrote:
>
> > Hmm. Maybe try yours with union instead of union all? Though if there's
> > only 1 path between any pair of nodes that shouldn't make too much
> > difference. Otherwise I'm getting low on ideas.
> >
> > What're the record counts for nodes 

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

[sqlite] sqlite3_column_name with alias

[Bart Smissaert]

Would it be possible somehow to get the column full name if there is a
column alias?

For example if we have:

select max(ID) as MaxID from table1

then I need to get max(ID) from the statement pointer.

One would think that if sqlite3_column_name can get the alias name then
somehow it must
be possible to get the real column name. I have very little knowledge of C,
so no idea how
I could do this by adding to the source.

RBS
___
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
Sent: 07 January 2018 18:05
To: 
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  on behalf of 
Luuk 
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