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] sqlite3_column_name with alias

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

[sqlite] sqlite3_column_name with alias

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

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

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

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

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

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'

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] sqlite3_column_name with alias

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

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] Efficient query to count number of leaves in a DAG.

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,

Re: [sqlite] Tip of Trunk does not compile under 64-bit MingW compiler

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

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] sqlite3_column_name with alias

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

Re: [sqlite] sqlite3_column_name with alias

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

Re: [sqlite] Retrieving constraint name

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

Re: [sqlite] The performance of indexed select

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

Re: [sqlite] The performance of indexed select

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

Re: [sqlite] The performance of indexed select

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

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

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