Re: [sqlite] Query optimizer and recursive common table expressions

Hello, On 2018-01-04 01:53, R Smith wrote: Not to mention that if you wait several years, depending on your processor/compiler, the integer 64 value might wrap around and x<=3 might become true once more, producing rows again  :) Unfortunately, it will be stuck when int becomes double (at

Re: [sqlite] Query optimizer and recursive common table expressions

On 2018/01/04 12:36 AM, Richard Hipp wrote: On 1/3/18, Shane Dev wrote: sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt) select * from cnt where x <= 3; [no sqlite> prompt, CPU utilization 25%] I assume sqlite is recursively adding rows to the

Re: [sqlite] [SPAM] Re: Emulate right-join

Hello, On 2017-12-06 19:07, R Smith wrote: You mean make SQLite less Lite, but with Zero computational advantage, by simply adding syntactic sugar bloat? - I'm going to have to vote No on that. (Luckily my vote counts extremely little.) I think the reason SQLite never implemented it is

Re: [sqlite] Query optimizer and recursive common table expressions

On 1/3/18, Shane Dev wrote: > > sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt) > select * from cnt where x <= 3; > [no sqlite> prompt, CPU utilization 25%] > > I assume sqlite is recursively adding rows to the queue without considering > that the

Re: [sqlite] Query optimizer and recursive common table expressions

I have just spotted a couple of typos in my email below. The first two common table expressions should have been as follows - with recursive cnt(x) as (select 1 union all select x+1 from cnt limit 3) select * from cnt; with recursive cnt(x) as (select 1 union all select x+1 from cnt) select *

[sqlite] Query optimizer and recursive common table expressions

Hi, This simple recursive common table expression returns all integers from 1 to 3 as expected - sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt limit 3) select * from cnt where x; x 1 2 3 sqlite> If the LIMIT constraint is moved from the compound SELECT to the

Re: [sqlite] Emulate right-join

In lieu of adding the syntactic sugar, might it be worth documenting the alternative(s)? Currently the docs for these are "https://sqlite.org/omitted.html; - which simply says: "LEFT OUTER JOIN is implemented, but not RIGHT OUTER JOIN or FULL OUTER JOIN." A couple of lines saying why this isn't

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

Playing devils advocate, there may be a purpose to those aliases (Application code requirement, which can't be changed), and/or, the single letter aliases are documented and understood throughout the application. But, you're absolutely correct. The only time I personally use single letter

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

A couple things... I recommend using longer names than 1 letter for your aliases, what you save in typing you lose a couple times over again when wondering what "r" is or why "t" has anything to do with "nodes" In your CTE you're doing a 3 table join. There's no need to include the nodes

Re: [sqlite] Loadable extension with shared state

On 03/01/2018 15:48, Richard Hipp wrote: On 1/3/18, Lifepillar wrote: Consider an extension that has some shared state, say a global `context` struct, whose value is used by a few user-defined SQL functions. Besides, assume that there are other SQL functions that can

Re: [sqlite] Loadable extension with shared state

On 1/3/18, Lifepillar wrote: > Consider an extension that has some shared state, say a global `context` > struct, whose value is used by a few user-defined SQL functions. > Besides, assume that there are other SQL functions that can act on the > global context. > > The

[sqlite] Loadable extension with shared state

Consider an extension that has some shared state, say a global `context` struct, whose value is used by a few user-defined SQL functions. Besides, assume that there are other SQL functions that can act on the global context. The question is: how do I turn this into a thread-safe extension?

Re: [sqlite] pragma table_info return column delimiters?

On 2018/01/03 12:15 PM, Bart Smissaert wrote: Is there a way with pragma table_info or otherwise (other than parsing the table create statement from SQLite_master) to get the column names including the column delimiters, eg double quotes or square brackets? So I would get eg: [column1]

Re: [sqlite] pragma table_info return column delimiters?

OK, thanks. I am getting the information now from the create table statements and sofar that seems to work OK. Just wanted to make sure there wasn't a better way to handle this. RBS On Wed, Jan 3, 2018 at 11:08 AM, Richard Hipp wrote: > On 1/3/18, Bart Smissaert

Re: [sqlite] pragma table_info return column delimiters?

On 2018/01/03 12:15 PM, Bart Smissaert wrote: Is there a way with pragma table_info or otherwise (other than parsing the table create statement from SQLite_master) to get the column names including the column delimiters, eg double quotes or square brackets? So I would get eg: [column1] [column2]

Re: [sqlite] pragma table_info return column delimiters?

delimiters delimit, so they are not part of the context.. such information aobut delimiters is never saved... since they havce done their job delimiiting already. Why do you want this? On Wed, Jan 3, 2018 at 2:50 AM, Bart Smissaert wrote: > > What are column

Re: [sqlite] pragma table_info return column delimiters?

On 1/3/18, Bart Smissaert wrote: > Is there a way with pragma table_info or otherwise (other than parsing the > table create statement from SQLite_master) to get the column names > including the column delimiters, eg double quotes or square brackets? So I > would get eg:

Re: [sqlite] pragma table_info return column delimiters?

> What are column delimiters? create table [table1)([ID] integer, [text_field] text) I am talking about the square brackets here surrounding the field names. Sorry, used the wrong word in saying delimiters. RBS On Wed, Jan 3, 2018 at 10:44 AM, Luuk wrote: > > > On 03-01-18

Re: [sqlite] pragma table_info return column delimiters?

On 03-01-18 11:15, Bart Smissaert wrote: > Is there a way with pragma table_info or otherwise (other than parsing the > table create statement from SQLite_master) to get the column names > including the column delimiters, eg double quotes or square brackets? So I > would get eg: [column1]

[sqlite] pragma table_info return column delimiters?

Is there a way with pragma table_info or otherwise (other than parsing the table create statement from SQLite_master) to get the column names including the column delimiters, eg double quotes or square brackets? So I would get eg: [column1] [column2] etc. if indeed the column names were delimited