Re: [sqlite] [EXTERNAL] get stmt row count

[Keith Medcalf]

Does not the statement of the problem define the answer?


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>Sent: Friday, 19 January, 2018 11:27
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>
>
>
>On 19 Jan 2018, at 3:22pm, David Raymond 
>wrote:
>
>> Keith & Simon, are you not both missing the point? I want the rows
>and the count but without having to run two queries.
>
>Yeah, I didn’t see that.  Sorry.
>
>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] [EXTERNAL] get stmt row count

[Keith Medcalf]

Thus you learned from the experience that the efficiency of your application 
programming lanuguage has far more effect than anything you might do 
fiddlefaddling with the query's.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of x
>Sent: Friday, 19 January, 2018 11:10
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>
>>(and possibly numeric index)
>
>
>
>You’re on my wavelength Ryan as, I think, is David.
>
>
>
>Incidentally, the stmt1 query was suited to the harvesting of base
>table RowIDs that I’ve been banging on about in other threads. For
>the query in question I replaced the column list with BaseTbl.RowID
>and ran the resultant query, It took just over 2 secs to store all
>the sorted base table RowIDs in a vector, the size of which yielded
>the row count.
>
>
>
>Compare that with the 12+ secs it took using the standard methods.
>Certainly sqlite was storing more of the query’s column data but It
>took only slightly longer using the RowID method to store all the
>results in a 2-dim vector of strings than it did to step through
>stmt1 and do the same.
>
>
>
>
>
>
>From: sqlite-users  on
>behalf of David Raymond 
>Sent: Friday, January 19, 2018 3:22:56 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>
>Maybe use a temp table as another possible solution?
>
>create temp table queryResults (id int);
>insert into queryResults select rowid from Tbl where Col > ?1 order
>by Col;
>select count(*) from queryResults;
>select id from queryResults order by rowid;
>drop table queryResults;
>
>
>
>The whole issue of CTE's being calculated more than once is an issue,
>otherwise I would say...
>
>with queryResults as (select rowid from Tbl where Col > ?1 order by
>Col)
>select count(*) from queryResults union all select rowid from
>queryResults;
>
>Then the first record would be the count, followed by all the rowids.
>But it looks like it's running the CTE twice, which defeats the
>advantage.
>
>
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of x
>Sent: Friday, January 19, 2018 8:50 AM
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>
>Keith & Simon, are you not both missing the point? I want the rows
>and the count but without having to run two queries.
>
>I tried the following (let stmt1 represent the original query and
>stmt2 the count(*) version of that query).
>
>stmt1 took 6+ secs for the first step.
>stmt2 took 6+ secs to get the count.
>
>Counting using
>
>int Count=1;
>while (sqlite3_step(stmt1)) Count++;
>
>after the first step took under 2 secs BUT in order to then get the
>rows I’d have to reset stmt1 which would result in the pre-first step
>code being run again at the first step call (another 6+ secs down the
>drain).
>
>I’m thinking (but I’m by no means sure) that sqlite’s pre-first step
>code should be able to calculate the row count as it went along at
>virtually no cost. I realise it would only be a solution for queries
>sorted by non-indexed columns (i.e. where sqlite’s pre-first step
>code had to consider all result set rows).
>
>
>
>
>
>___
>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
>___
>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] [EXTERNAL] get stmt row count

[Keith Medcalf]

The trouble is that it is only "by happenstance" that you can obtain the 
count() after a single step.  There may be cases where you cannot, or where the 
rows are ordered by subsorted blocks, or by other mythical magical creatures 
that return some "random" number of computer results per step.

Executing a query returns one row for each step.  How many rows are availalbe 
"in the background" in one call to step is a detail of magic upon which you 
cannot rely.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of x
>Sent: Friday, 19 January, 2018 08:50
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>
>Keith & Simon, are you not both missing the point? I want the rows
>and the count but without having to run two queries.
>
>I tried the following (let stmt1 represent the original query and
>stmt2 the count(*) version of that query).
>
>stmt1 took 6+ secs for the first step.
>stmt2 took 6+ secs to get the count.
>
>Counting using
>
>int Count=1;
>while (sqlite3_step(stmt1)) Count++;
>
>after the first step took under 2 secs BUT in order to then get the
>rows I’d have to reset stmt1 which would result in the pre-first step
>code being run again at the first step call (another 6+ secs down the
>drain).
>
>I’m thinking (but I’m by no means sure) that sqlite’s pre-first step
>code should be able to calculate the row count as it went along at
>virtually no cost. I realise it would only be a solution for queries
>sorted by non-indexed columns (i.e. where sqlite’s pre-first step
>code had to consider all result set rows).
>
>
>
>
>
>___
>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] SQL frequency of names in 2 tables

[Bart Smissaert]

Thanks for that, very nice indeed!
I wasn't aware of the using keyword and will need to look that up.

RBS

On Fri, Jan 19, 2018 at 10:58 PM, David Raymond 
wrote:

> Since they both have the same set of names, then something like the below.
> Again, since both have all the names there won't be a divide by 0 error to
> worry about for the percentage.
>
> select
> name as names, Table1_Count, Table2_Count, 1.0 * Table1_Count /
> Table2_Count as percentage
> from
> (select name, count(*) as Table1_Count
> from table1
> group by name
> ) as table1counts
> inner join
> (select name, count(*) as Table2_Count
> from table2
> group by name
> ) as table2counts
> using (name)
> order by names;--optional
>
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Bart Smissaert
> Sent: Friday, January 19, 2018 5:41 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] SQL frequency of names in 2 tables
>
> Say we have 2 tables, each with a text column, holding non-unique names.
> All names in table 1 are also in table 2 and vice-versa.
> The frequency of the names are different for both tables and this is the
> information
> I need to get.
>
> So output should be like this:
>
> Names   Table1_Count   Table2_Count   Percentage
> 
> Name13   9   33.33
> Name2 1  10  0.1
>
> I am sure I am overlooking something simple, but not seen it yet.
> Thanks for any idea.
>
>
> RBS
> ___
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL frequency of names in 2 tables

[David Raymond]

Since they both have the same set of names, then something like the below. 
Again, since both have all the names there won't be a divide by 0 error to 
worry about for the percentage.

select
name as names, Table1_Count, Table2_Count, 1.0 * Table1_Count / Table2_Count as 
percentage
from
(select name, count(*) as Table1_Count
from table1
group by name
) as table1counts
inner join
(select name, count(*) as Table2_Count
from table2
group by name
) as table2counts
using (name)
order by names;--optional



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Bart Smissaert
Sent: Friday, January 19, 2018 5:41 PM
To: General Discussion of SQLite Database
Subject: [sqlite] SQL frequency of names in 2 tables

Say we have 2 tables, each with a text column, holding non-unique names.
All names in table 1 are also in table 2 and vice-versa.
The frequency of the names are different for both tables and this is the
information
I need to get.

So output should be like this:

Names   Table1_Count   Table2_Count   Percentage

Name13   9   33.33
Name2 1  10  0.1

I am sure I am overlooking something simple, but not seen it yet.
Thanks for any idea.


RBS
___
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

[sqlite] SQL frequency of names in 2 tables

[Bart Smissaert]

Say we have 2 tables, each with a text column, holding non-unique names.
All names in table 1 are also in table 2 and vice-versa.
The frequency of the names are different for both tables and this is the
information
I need to get.

So output should be like this:

Names   Table1_Count   Table2_Count   Percentage

Name13   9   33.33
Name2 1  10  0.1

I am sure I am overlooking something simple, but not seen it yet.
Thanks for any idea.


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

Re: [sqlite] WAL and pragma uncommitted

[Simon Slavin]

On 19 Jan 2018, at 4:46pm, Deon Brewis  wrote:

> If you start with:
> INSERT INTO Woz(Foo, Bar) Values(1,1)
> 
> And a (normal) writer thread updates the 2 columns:
> UPDATE Woz SET Foo=2, Bar=2
> 
> Can a read_uncommitted thread read the value from the row as:
> Foo=1, Bar=2 
> ?

No.  The very least you can get is statement-level consistency.  If that PRAGMA 
is on you might get (!,1) or (2,2), you just can’t predict which you’ll get.

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

Re: [sqlite] WAL and pragma uncommitted

[Clemens Ladisch]

Deon Brewis wrote:
> What is the level of consistency (or rather inconsistency) for 
> read_uncommitted?

In read_uncommited mode, read-only transactions to not take the database
file lock.  However, most sqlite3_xxx() function calls still lock the
in-memory database object(s) (this is required for any multi-threaded
accesses), so other threads can see only the complete changes made
while a lock was held.  In particular, sqlite3_step() takes a single
lock around all VDBE statements it executes, so what you end up with is
statement-level atomicy.


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

Re: [sqlite] WAL and pragma uncommitted

[Clemens Ladisch]

Hannah Massey wrote:
> If I use separate connections for the reading threads then is there an
> advantage to using "shared cache" for those connections?

The shared cache would be useful to reduce memory usage (which should
not be a concern except in embedded systems), but concurrent accesses to
the same data structure need locking.  This locking is done inside most
sqlite3_xxx() function calls (see sqlite3_db_mutex()), and independent
from the transaction locking.

So for maximum performance, use one connection per thread without
shared-cache mode.  This allows you to run all reading threads in
parallel, even when they are accessing the same database, and to use
SQLITE_OPEN_NOMUTEX to avoid the locking overhead.  (If the lock is
not actually contested, the locking overhead would probably be too
small to notice.)


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

Re: [sqlite] [EXTERNAL] get stmt row count

[x]

Not unusually for me I may have caused confusion in my earlier posts. In my 
opening post I mentioned the query



SELECT RowID from Tbl where Col > ?1 order by Col; // there’s no index on Col



However, the “moderately complex query” I mentioned in my reply to Gunter is of 
the following form



select ID1, ID2, ID3, ID4, IntColA, IntColB, TextCol2, TextCol3, TextCol4 from

BaseTbl

left join Tbl2 using (ID2)

left join Tbl3 using (ID3)

left join Tbl4 using (ID4)

order by Tbl2.NonIndexedTextCol;



and this is the query I’ve been referring to (and quoting timings for) since. 
The query



select BaseTbl.RowID from

BaseTbl

left join Tbl2 using (ID2)

left join Tbl3 using (ID3)

left join Tbl4 using (ID4)

order by Tbl2.NonIndexedTextCol;



is shortened to



select BaseTbl.RowID from

BaseTbl

left join Tbl2 using (ID2)

order by Tbl2.NonIndexedTextCol;



by the query planner. The query is therefore slightly biased towards my base 
table RowIDs modus operandi.










From: sqlite-users  on behalf of 
x 
Sent: Friday, January 19, 2018 4:09:49 PM
To: SQLite mailing list
Subject: Re: [sqlite] [EXTERNAL] get stmt row count

>(and possibly numeric index)



You’re on my wavelength Ryan as, I think, is David.



Incidentally, the stmt1 query was suited to the harvesting of base table RowIDs 
that I’ve been banging on about in other threads. For the query in question I 
replaced the column list with BaseTbl.RowID and ran the resultant query, It 
took just over 2 secs to store all the sorted base table RowIDs in a vector, 
the size of which yielded the row count.



Compare that with the 12+ secs it took using the standard methods. Certainly 
sqlite was storing more of the query’s column data but It took only slightly 
longer using the RowID method to store all the results in a 2-dim vector of 
strings than it did to step through stmt1 and do the same.






From: sqlite-users  on behalf of 
David Raymond 
Sent: Friday, January 19, 2018 3:22:56 PM
To: SQLite mailing list
Subject: Re: [sqlite] [EXTERNAL] get stmt row count

Maybe use a temp table as another possible solution?

create temp table queryResults (id int);
insert into queryResults select rowid from Tbl where Col > ?1 order by Col;
select count(*) from queryResults;
select id from queryResults order by rowid;
drop table queryResults;



The whole issue of CTE's being calculated more than once is an issue, otherwise 
I would say...

with queryResults as (select rowid from Tbl where Col > ?1 order by Col)
select count(*) from queryResults union all select rowid from queryResults;

Then the first record would be the count, followed by all the rowids. But it 
looks like it's running the CTE twice, which defeats the advantage.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of x
Sent: Friday, January 19, 2018 8:50 AM
To: SQLite mailing list
Subject: Re: [sqlite] [EXTERNAL] get stmt row count

Keith & Simon, are you not both missing the point? I want the rows and the 
count but without having to run two queries.

I tried the following (let stmt1 represent the original query and stmt2 the 
count(*) version of that query).

stmt1 took 6+ secs for the first step.
stmt2 took 6+ secs to get the count.

Counting using

int Count=1;
while (sqlite3_step(stmt1)) Count++;

after the first step took under 2 secs BUT in order to then get the rows I’d 
have to reset stmt1 which would result in the pre-first step code being run 
again at the first step call (another 6+ secs down the drain).

I’m thinking (but I’m by no means sure) that sqlite’s pre-first step code 
should be able to calculate the row count as it went along at virtually no 
cost. I realise it would only be a solution for queries sorted by non-indexed 
columns (i.e. where sqlite’s pre-first step code had to consider all result set 
rows).





___
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
___
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] WAL and pragma uncommitted

[Deon Brewis]

What is the level of consistency (or rather inconsistency) for read_uncommitted?

If you start with:
INSERT INTO Woz(Foo, Bar) Values(1,1)

And a (normal) writer thread updates the 2 columns:
UPDATE Woz SET Foo=2, Bar=2

Can a read_uncommitted thread read the value from the row as:
Foo=1, Bar=2 
?


And if so, what about something like:
UPDATE Woz SET Foo=2, Bar=x'12345789'

Can a read_uncommitted thread read:
Foo=x'1234'

i.e. A partially updated column?


I would assume that for a transactional update across 2 rows all bets will be 
off.

What if a row was deleted, can a read_uncommitted read cause a crash?

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Friday, January 19, 2018 8:36 AM
To: SQLite mailing list 
Subject: Re: [sqlite] WAL and pragma uncommitted

On 19 Jan 2018, at 4:26pm, Hannah Massey  wrote:

> Will #pragma uncommitted work in WAL mode and will it have the effect 
> I'm looking for (where the read will be faster because it can ignore 
> the recently written information in the WAL File) and simply use the 
> database file only?

The command you’re talking about is

PRAGMA read_uncommitted = boolean

You may find you don’t need it.  Switch to WAL mode and try it without that 
PRAGMA.  It works more or less the way you described: one connection can write 
while other read, and nothing will block anything else.

Pay a lot of attention to how many connections you’re using.  You might want 
one connection for the writing, and another for all the reading threads.  Or 
you might want separate connections for the reading threads too.  Testing 
various approaches will let you find the solution which best suits your 
programming and timing requirements.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users=02%7C01%7C%7C7304a7658ee04115fe6508d55f5abc8e%7C84df9e7fe9f640afb435%7C1%7C0%7C636519765662668955=o6sR0H3YrZMdFUm9OwJ3CjyYi3J9s0MapK2%2BBeoiBuY%3D=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL and pragma uncommitted

[Hannah Massey]

ok thanks for your advice. If I use separate connections for the reading
threads then is there an advantage to using "shared cache" for those
connections?

On 19 January 2018 at 16:35, Simon Slavin  wrote:

> On 19 Jan 2018, at 4:26pm, Hannah Massey  wrote:
>
> > Will #pragma uncommitted work in WAL mode and will it have the effect I'm
> > looking for (where the read will be faster because it can ignore the
> > recently written information in the WAL File) and simply use the database
> > file only?
>
> The command you’re talking about is
>
> PRAGMA read_uncommitted = boolean
>
> You may find you don’t need it.  Switch to WAL mode and try it without
> that PRAGMA.  It works more or less the way you described: one connection
> can write while other read, and nothing will block anything else.
>
> Pay a lot of attention to how many connections you’re using.  You might
> want one connection for the writing, and another for all the reading
> threads.  Or you might want separate connections for the reading threads
> too.  Testing various approaches will let you find the solution which best
> suits your programming and timing requirements.
>
> 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] WAL and pragma uncommitted

[Simon Slavin]

On 19 Jan 2018, at 4:26pm, Hannah Massey  wrote:

> Will #pragma uncommitted work in WAL mode and will it have the effect I'm
> looking for (where the read will be faster because it can ignore the
> recently written information in the WAL File) and simply use the database
> file only?

The command you’re talking about is

PRAGMA read_uncommitted = boolean

You may find you don’t need it.  Switch to WAL mode and try it without that 
PRAGMA.  It works more or less the way you described: one connection can write 
while other read, and nothing will block anything else.

Pay a lot of attention to how many connections you’re using.  You might want 
one connection for the writing, and another for all the reading threads.  Or 
you might want separate connections for the reading threads too.  Testing 
various approaches will let you find the solution which best suits your 
programming and timing requirements.

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

[sqlite] WAL and pragma uncommitted

[Hannah Massey]

Currently we access a single SQLite database in a single thread but I am
working on changing this as performance has become a real problem. We will
be using WAL mode and there will be one thread for writes and multiple
threads for reads. For many cases, speed will be of a priority and it will
not matter if the data returned from a read is slightly out of date so I
can considering using #pragma uncommitted in some of the reader threads.
Will #pragma uncommitted work in WAL mode and will it have the effect I'm
looking for (where the read will be faster because it can ignore the
recently written information in the WAL File) and simply use the database
file only?

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

Re: [sqlite] [EXTERNAL] get stmt row count

[Simon Slavin]

On 19 Jan 2018, at 3:22pm, David Raymond  wrote:

> Keith & Simon, are you not both missing the point? I want the rows and the 
> count but without having to run two queries.

Yeah, I didn’t see that.  Sorry.

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

Re: [sqlite] [EXTERNAL] get stmt row count

[x]

>(and possibly numeric index)



You’re on my wavelength Ryan as, I think, is David.



Incidentally, the stmt1 query was suited to the harvesting of base table RowIDs 
that I’ve been banging on about in other threads. For the query in question I 
replaced the column list with BaseTbl.RowID and ran the resultant query, It 
took just over 2 secs to store all the sorted base table RowIDs in a vector, 
the size of which yielded the row count.



Compare that with the 12+ secs it took using the standard methods. Certainly 
sqlite was storing more of the query’s column data but It took only slightly 
longer using the RowID method to store all the results in a 2-dim vector of 
strings than it did to step through stmt1 and do the same.






From: sqlite-users  on behalf of 
David Raymond 
Sent: Friday, January 19, 2018 3:22:56 PM
To: SQLite mailing list
Subject: Re: [sqlite] [EXTERNAL] get stmt row count

Maybe use a temp table as another possible solution?

create temp table queryResults (id int);
insert into queryResults select rowid from Tbl where Col > ?1 order by Col;
select count(*) from queryResults;
select id from queryResults order by rowid;
drop table queryResults;



The whole issue of CTE's being calculated more than once is an issue, otherwise 
I would say...

with queryResults as (select rowid from Tbl where Col > ?1 order by Col)
select count(*) from queryResults union all select rowid from queryResults;

Then the first record would be the count, followed by all the rowids. But it 
looks like it's running the CTE twice, which defeats the advantage.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of x
Sent: Friday, January 19, 2018 8:50 AM
To: SQLite mailing list
Subject: Re: [sqlite] [EXTERNAL] get stmt row count

Keith & Simon, are you not both missing the point? I want the rows and the 
count but without having to run two queries.

I tried the following (let stmt1 represent the original query and stmt2 the 
count(*) version of that query).

stmt1 took 6+ secs for the first step.
stmt2 took 6+ secs to get the count.

Counting using

int Count=1;
while (sqlite3_step(stmt1)) Count++;

after the first step took under 2 secs BUT in order to then get the rows I’d 
have to reset stmt1 which would result in the pre-first step code being run 
again at the first step call (another 6+ secs down the drain).

I’m thinking (but I’m by no means sure) that sqlite’s pre-first step code 
should be able to calculate the row count as it went along at virtually no 
cost. I realise it would only be a solution for queries sorted by non-indexed 
columns (i.e. where sqlite’s pre-first step code had to consider all result set 
rows).





___
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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] get stmt row count

[David Raymond]

Maybe use a temp table as another possible solution?

create temp table queryResults (id int);
insert into queryResults select rowid from Tbl where Col > ?1 order by Col;
select count(*) from queryResults;
select id from queryResults order by rowid;
drop table queryResults;



The whole issue of CTE's being calculated more than once is an issue, otherwise 
I would say...

with queryResults as (select rowid from Tbl where Col > ?1 order by Col)
select count(*) from queryResults union all select rowid from queryResults;

Then the first record would be the count, followed by all the rowids. But it 
looks like it's running the CTE twice, which defeats the advantage.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of x
Sent: Friday, January 19, 2018 8:50 AM
To: SQLite mailing list
Subject: Re: [sqlite] [EXTERNAL] get stmt row count

Keith & Simon, are you not both missing the point? I want the rows and the 
count but without having to run two queries.

I tried the following (let stmt1 represent the original query and stmt2 the 
count(*) version of that query).

stmt1 took 6+ secs for the first step.
stmt2 took 6+ secs to get the count.

Counting using

int Count=1;
while (sqlite3_step(stmt1)) Count++;

after the first step took under 2 secs BUT in order to then get the rows I’d 
have to reset stmt1 which would result in the pre-first step code being run 
again at the first step call (another 6+ secs down the drain).

I’m thinking (but I’m by no means sure) that sqlite’s pre-first step code 
should be able to calculate the row count as it went along at virtually no 
cost. I realise it would only be a solution for queries sorted by non-indexed 
columns (i.e. where sqlite’s pre-first step code had to consider all result set 
rows).





___
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] "Error: NOT NULL constraint failed ..." when no such constraint exists

[Simon Slavin]

On 19 Jan 2018, at 1:48pm, R Smith  wrote:

> On 2018/01/19 3:36 PM, Simon Slavin wrote:
> 
>> I hope one day to see SQLite4, in which everything done to avoid breaking 
>> backward compatibility is abandoned. 
> 
> That's an impossibility.
> 
> If you mean that SQLite4 itself will not have to honour backwards 
> compatibility to SQLite3, then yes - but if you intended that SQLite4 itself 
> will have a mechanism by which it will not fall prey to the perils of 
> maintaining backwards compatibility to its own generation, then no, that is 
> impossible. It too will have the same struggle.

Oh no, I get that.  SQLite4 will start its own legacy of foibles.  But it would 
be nice to get rid of the "" quoted variable names, the NULL keys, and a few 
other things.

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

Re: [sqlite] Defect: Redundant CTE table materialization gives wrong answer.

[R Smith]

On 2018/01/19 11:30 AM, petern wrote:

You're entitled to your opinion but I'd rather hear from somebody with
actual source check-in privileges.


Perhaps mail a dev directly then?  On here you will never satisfy that 
requirement.



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

Re: [sqlite] [EXTERNAL] get stmt row count

[R Smith]

On 2018/01/19 2:16 PM, Simon Slavin wrote:


On 19 Jan 2018, at 12:06pm, x  wrote:


Int Count=0;

while (sqlite3_step(...)) Count++;

That’s a slow way to count rows.  Do it in SQL:

SELECT COUNT(*) FROM MyTable WHERE b > 11;

You get just one row back which makes everything far faster.


I think the OP is not interested in the actual row count for that 
specific query, rather he is interested in a mechanism by which he can 
receive both the rows and their count (and possibly numeric index) in 
the space of a single query, hence the counter while looping the cursor 
- which is in fact the very best way to do it.



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

Re: [sqlite] [EXTERNAL] get stmt row count

[x]

Keith & Simon, are you not both missing the point? I want the rows and the 
count but without having to run two queries.

I tried the following (let stmt1 represent the original query and stmt2 the 
count(*) version of that query).

stmt1 took 6+ secs for the first step.
stmt2 took 6+ secs to get the count.

Counting using

int Count=1;
while (sqlite3_step(stmt1)) Count++;

after the first step took under 2 secs BUT in order to then get the rows I’d 
have to reset stmt1 which would result in the pre-first step code being run 
again at the first step call (another 6+ secs down the drain).

I’m thinking (but I’m by no means sure) that sqlite’s pre-first step code 
should be able to calculate the row count as it went along at virtually no 
cost. I realise it would only be a solution for queries sorted by non-indexed 
columns (i.e. where sqlite’s pre-first step code had to consider all result set 
rows).





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

Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

[R Smith]

On 2018/01/19 3:36 PM, Simon Slavin wrote:

On 19 Jan 2018, at 12:43pm, Richard Hipp  wrote:

I hope one day to see SQLite4, in which everything done to avoid 
breaking backward compatibility is abandoned. 


That's an impossibility.

If you mean that SQLite4 itself will not have to honour backwards 
compatibility to SQLite3, then yes - but if you intended that SQLite4 
itself will have a mechanism by which it will not fall prey to the 
perils of maintaining backwards compatibility to its own generation, 
then no, that is impossible. It too will have the same struggle.


One can hope though that, by that time, so many SQL-standard things have 
been fixed that it won't matter much anymore.



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

Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

[Simon Slavin]

On 19 Jan 2018, at 12:43pm, Richard Hipp  wrote:

> Sometimes one has to compromise the simplicity of the
> design, or to document bugs rather than fix them, in order to avoid
> breaking legacy applications.

I hope one day to see SQLite4, in which everything done to avoid breaking 
backward compatibility is abandoned.

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

Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

[Richard Hipp]

On 1/19/18, Shane Dev  wrote:
> I missed that part of the documentation, thanks

I apologize for this goofy exception to the rules.  Maintaining a
widely-used library like SQLite in a way that is backwards compatible
is difficult.  Sometimes one has to compromise the simplicity of the
design, or to document bugs rather than fix them, in order to avoid
breaking legacy applications.  This is such an instance.  There are
others, which you will no doubt encounter from time to time if you use
SQLite intensely.
-- 
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] [EXTERNAL] get stmt row count

[Simon Slavin]

On 19 Jan 2018, at 12:06pm, x  wrote:

> Int Count=0;
> 
> while (sqlite3_step(...)) Count++;

That’s a slow way to count rows.  Do it in SQL:

SELECT COUNT(*) FROM MyTable WHERE b > 11;

You get just one row back which makes everything far faster.

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

Re: [sqlite] [EXTERNAL] get stmt row count

[Keith Medcalf]

Then you should have asked for the count(*) which would have returned the count 
rather than you having to count the rows as they are returned.  

Make up your mind what you want -- and ye shall get that for which you asked -- 
the rows or the count of the rows?  Getting both requires TWO queries (and may 
not be the same from one nanosecond to the next).  Or you can request the rows 
AND count them if you need both the rows and the count of them.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of x
>Sent: Friday, 19 January, 2018 07:07
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>
>Gunter, I just ran a moderately complex query sorted on a non-indexed
>column which returned 2.4 million rows. As you say, after the first
>step practically all of the work is done yet it still took almost 2
>secs to run
>
>
>
>Int Count=0;
>
>while (sqlite3_step(...)) Count++;
>
>
>
>that’s on a laptop with an SSD and 16 GB RAM. It’s not exactly
>insignificant if you’re looking to set up a grid scroll bar.
>
>
>
>Tom
>
>
>
>
>From: sqlite-users  on
>behalf of Hick Gunter 
>Sent: Friday, January 19, 2018 10:27:30 AM
>To: 'SQLite mailing list'
>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>
>No. The only viable query plan is scan and sort (see "explain query
>plan" output). The sqlite3_prepare() family of calls creates the SQL
>program (see "explain" output) and sets ist initial state. If the
>value you require is not null, you must call one of the
>sqlite3_bind() functions. This sets the value of the variable (line
>2). Calling sqlite3_step() on the statement causes the program to run
>up to either the ResultRow or the Halt statement. By the time the
>firs call to sqlite3_step() returns, all the table rows have been
>read, the values sorted and the first output row produced. This is
>practically all of the work. Stopping now saves nothing.
>
>There is no count of "records selected from base table" or "records
>inserted into sorter". Keeping such scores would add work to be done,
>with no benefit except perhaps satisfying your intellectual
>curiosity.
>
>asql> create temp table test (rowid integer primary key, value
>integer);
>asql> .explain
>asql> explain query plan select rowid from test where value > ?1
>order by value;
>sele  order  from  deta
>  -    
>0 0  0 SCAN TABLE test (~33 rows)
>0 0  0 USE TEMP B-TREE FOR ORDER BY
>asql> explain select rowid from test where value > ?1 order by value;
>addr  opcode p1p2p3p4 p5  comment
>  -        -  --  ---
>--
>0 Trace  0 0 000  NULL
>1 SorterOpen 1 3 0 Keyinfo(1,BINARY)  00  NULL
>2 Variable   1 1 0 ?1 00  NULL
>3 Goto   0 27000  NULL
>4 OpenRead   0 2 1 2  00  test
>5 Rewind 0 16000  NULL
>6 Column 0 1 200  test.value
>7 Le 1 152 collseq(BINARY)  6c  NULL
>8 Rowid  0 4 000  NULL
>9 MakeRecord 4 1 200  NULL
>10Column 0 1 500  test.value
>11Sequence   1 6 000  NULL
>12Move   2 7 100  NULL
>13MakeRecord 5 3 300  NULL
>14SorterInsert   1 3 000  NULL
>15Next   0 6 001  NULL
>16Close  0 0 000  NULL
>17OpenPseudo 2 2 100  NULL
>18OpenPseudo 3 8 300  NULL
>19SorterSort 1 25000  NULL
>20SorterData 1 8 000  NULL
>21Column 3 2 220  NULL
>22Column 2 0 420  NULL
>23ResultRow  4 1 000  NULL
>24SorterNext 1 20000  NULL
>25Close  2 0 000  NULL
>26Halt   0 0 000  NULL
>27Transaction1 0 000  NULL
>28VerifyCookie   1 1 000  NULL
>29TableLock  1 2 0 test   00  NULL
>30Goto   0 4 000  NULL
>
>-Ursprüngliche 

Re: [sqlite] [EXTERNAL] get stmt row count

[x]

Gunter, I just ran a moderately complex query sorted on a non-indexed column 
which returned 2.4 million rows. As you say, after the first step practically 
all of the work is done yet it still took almost 2 secs to run



Int Count=0;

while (sqlite3_step(...)) Count++;



that’s on a laptop with an SSD and 16 GB RAM. It’s not exactly insignificant if 
you’re looking to set up a grid scroll bar.



Tom




From: sqlite-users  on behalf of 
Hick Gunter 
Sent: Friday, January 19, 2018 10:27:30 AM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] get stmt row count

No. The only viable query plan is scan and sort (see "explain query plan" 
output). The sqlite3_prepare() family of calls creates the SQL program (see 
"explain" output) and sets ist initial state. If the value you require is not 
null, you must call one of the sqlite3_bind() functions. This sets the value of 
the variable (line 2). Calling sqlite3_step() on the statement causes the 
program to run up to either the ResultRow or the Halt statement. By the time 
the firs call to sqlite3_step() returns, all the table rows have been read, the 
values sorted and the first output row produced. This is practically all of the 
work. Stopping now saves nothing.

There is no count of "records selected from base table" or "records inserted 
into sorter". Keeping such scores would add work to be done, with no benefit 
except perhaps satisfying your intellectual curiosity.

asql> create temp table test (rowid integer primary key, value integer);
asql> .explain
asql> explain query plan select rowid from test where value > ?1 order by value;
sele  order  from  deta
  -    
0 0  0 SCAN TABLE test (~33 rows)
0 0  0 USE TEMP B-TREE FOR ORDER BY
asql> explain select rowid from test where value > ?1 order by value;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000  NULL
1 SorterOpen 1 3 0 Keyinfo(1,BINARY)  00  NULL
2 Variable   1 1 0 ?1 00  NULL
3 Goto   0 27000  NULL
4 OpenRead   0 2 1 2  00  test
5 Rewind 0 16000  NULL
6 Column 0 1 200  test.value
7 Le 1 152 collseq(BINARY)  6c  NULL
8 Rowid  0 4 000  NULL
9 MakeRecord 4 1 200  NULL
10Column 0 1 500  test.value
11Sequence   1 6 000  NULL
12Move   2 7 100  NULL
13MakeRecord 5 3 300  NULL
14SorterInsert   1 3 000  NULL
15Next   0 6 001  NULL
16Close  0 0 000  NULL
17OpenPseudo 2 2 100  NULL
18OpenPseudo 3 8 300  NULL
19SorterSort 1 25000  NULL
20SorterData 1 8 000  NULL
21Column 3 2 220  NULL
22Column 2 0 420  NULL
23ResultRow  4 1 000  NULL
24SorterNext 1 20000  NULL
25Close  2 0 000  NULL
26Halt   0 0 000  NULL
27Transaction1 0 000  NULL
28VerifyCookie   1 1 000  NULL
29TableLock  1 2 0 test   00  NULL
30Goto   0 4 000  NULL

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von x
Gesendet: Freitag, 19. Jänner 2018 10:43
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] get stmt row count

I’ve read up on the subject and realise that in most cases there’s no way of 
getting the row count without stepping through all the rows. That said, is 
there not times when sqlite could help to avoid this? Take the following simple 
query

SELECT RowID from Tbl where Col > ?1 order by Col; // there’s no index on Col

I’m assuming sqlite can’t complete the first step without first obtaining and 
sorting the result set and that, in doing so, it already knows the row count. 
Is there any way of obtaining this or is there a case for a function

sqlite3_row_count(stmt)

which returns -1 when it can’t be determined but otherwise completes the pre 
first step 

Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

[Keith Medcalf]

A Primary Key cannot be null.  Except in cases of bug-for-bug backwards 
compatibility.  WITHOUT ROWID tables comply with the SQL specification, not the 
bug-for-bug compatibility mode of rowid tables where due to an age old error 
nulls are allowed in primary keys although they should not be.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Shane Dev
>Sent: Friday, 19 January, 2018 01:32
>To: SQLite mailing list
>Subject: [sqlite] "Error: NOT NULL constraint failed ..." when no
>such constraint exists
>
>Hello,
>
>The following SQL works as I expect -
>
>sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
>child));
>sqlite> insert into edges select null, 1;
>sqlite> select * from edges;
>parent  child
>1
>sqlite>
>
>but if I remove the superfluous rowid column from the table
>definition -
>
>sqlite> drop table edges;
>sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
>child)) without rowid;
>sqlite> insert into edges select null, 1;
>Error: NOT NULL constraint failed: edges.parent
>sqlite>
>
>Why do I get this error?
>___
>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

[sqlite] About test_vfs.c in sqlite test

[Nick]

I find a file named test_vfs.c when I run the tcl tests in the source tree.
When I open a wal-file with a sqlite3_file* file descriptor pFile1, it
called sqlite3OsOpen(). The call tree is like the pic below:

sqlite3OsOpen(pFile1)
  |
  |
 pVfs->xOpen ==> tvfsOpen
  |
  |
 sqlite3OsOpen(pFile2)
  |
  |
  pVfs->xOpen ==> unixOpen(pFile2)

In some tests, test_vfs.c is involved and pVfs->xOpen() will bind to
tvfsOpen() instead of unixOpen() directly. 
And I find the address of pFile has changed to pFile2 when sqlite3OsOpen()
is called in the second time. 
Then unixOpen will initialize pFile2, such as set nFetchOut and some other
member elements in struct unixFile to 0. But the nFetchOut of pFile1 may not
be 0 when sqlite3OsOpen(pFile1) returns.

It makes me confused as I find db will not crash even if all the member
elements of the unixFile is not correct. Could it be said that these
elements will not be used before they are set to a right value?
And what is test_vfs.c for?

I am new bee to sqlite test and vfs. Could anyone explain me it is correct
that the nFetchOut of pFile1 is not 0 when sqlite3OsOpen(pFile1) returns? 

I am really confused. Thanks for any light you can shed. 



--
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] [EXTERNAL] get stmt row count

[Hick Gunter]

No. The only viable query plan is scan and sort (see "explain query plan" 
output). The sqlite3_prepare() family of calls creates the SQL program (see 
"explain" output) and sets ist initial state. If the value you require is not 
null, you must call one of the sqlite3_bind() functions. This sets the value of 
the variable (line 2). Calling sqlite3_step() on the statement causes the 
program to run up to either the ResultRow or the Halt statement. By the time 
the firs call to sqlite3_step() returns, all the table rows have been read, the 
values sorted and the first output row produced. This is practically all of the 
work. Stopping now saves nothing.

There is no count of "records selected from base table" or "records inserted 
into sorter". Keeping such scores would add work to be done, with no benefit 
except perhaps satisfying your intellectual curiosity.

asql> create temp table test (rowid integer primary key, value integer);
asql> .explain
asql> explain query plan select rowid from test where value > ?1 order by value;
sele  order  from  deta
  -    
0 0  0 SCAN TABLE test (~33 rows)
0 0  0 USE TEMP B-TREE FOR ORDER BY
asql> explain select rowid from test where value > ?1 order by value;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000  NULL
1 SorterOpen 1 3 0 Keyinfo(1,BINARY)  00  NULL
2 Variable   1 1 0 ?1 00  NULL
3 Goto   0 27000  NULL
4 OpenRead   0 2 1 2  00  test
5 Rewind 0 16000  NULL
6 Column 0 1 200  test.value
7 Le 1 152 collseq(BINARY)  6c  NULL
8 Rowid  0 4 000  NULL
9 MakeRecord 4 1 200  NULL
10Column 0 1 500  test.value
11Sequence   1 6 000  NULL
12Move   2 7 100  NULL
13MakeRecord 5 3 300  NULL
14SorterInsert   1 3 000  NULL
15Next   0 6 001  NULL
16Close  0 0 000  NULL
17OpenPseudo 2 2 100  NULL
18OpenPseudo 3 8 300  NULL
19SorterSort 1 25000  NULL
20SorterData 1 8 000  NULL
21Column 3 2 220  NULL
22Column 2 0 420  NULL
23ResultRow  4 1 000  NULL
24SorterNext 1 20000  NULL
25Close  2 0 000  NULL
26Halt   0 0 000  NULL
27Transaction1 0 000  NULL
28VerifyCookie   1 1 000  NULL
29TableLock  1 2 0 test   00  NULL
30Goto   0 4 000  NULL

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von x
Gesendet: Freitag, 19. Jänner 2018 10:43
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] get stmt row count

I’ve read up on the subject and realise that in most cases there’s no way of 
getting the row count without stepping through all the rows. That said, is 
there not times when sqlite could help to avoid this? Take the following simple 
query

SELECT RowID from Tbl where Col > ?1 order by Col; // there’s no index on Col

I’m assuming sqlite can’t complete the first step without first obtaining and 
sorting the result set and that, in doing so, it already knows the row count. 
Is there any way of obtaining this or is there a case for a function

sqlite3_row_count(stmt)

which returns -1 when it can’t be determined but otherwise completes the pre 
first step code and returns the row count?

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org

[sqlite] get stmt row count

[x]

I’ve read up on the subject and realise that in most cases there’s no way of 
getting the row count without stepping through all the rows. That said, is 
there not times when sqlite could help to avoid this? Take the following simple 
query

SELECT RowID from Tbl where Col > ?1 order by Col; // there’s no index on Col

I’m assuming sqlite can’t complete the first step without first obtaining and 
sorting the result set and that, in doing so, it already knows the row count. 
Is there any way of obtaining this or is there a case for a function

sqlite3_row_count(stmt)

which returns -1 when it can’t be determined but otherwise completes the pre 
first step code and returns the row count?

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

Re: [sqlite] Defect: Redundant CTE table materialization gives wrong answer.

[petern]

Another implementation detail?  I was wondering where you were, Clemens.
You're entitled to your opinion but I'd rather hear from somebody with
actual source check-in privileges.

I've posted at least 5 legitimate defects which triangulate on a
consistency problem when trying to access properties of the current
statement by any of CTE, cross join, aux_data, pointer, and subtype API's.

What response did I get?  Crickets - and then Clemens telling me I'm
imagining things because these are ALL implementation details.

Well, Clemens you are definitely wrong about the CTE.  If table aliases of
CTE's were intended as ordinary independent views, the CTE syntax makes no
sense.
Every other implementation agrees with the expected set-mathematical
definition.   SQLite is producing the wrong answer and I'm looking forward
to seeing the trouble ticket that fixes this problem.

Peter








On Fri, Jan 19, 2018 at 12:10 AM, Clemens Ladisch 
wrote:

> petern wrote:
> > WITH flips(s) AS (VALUES (random()>0), (random()>0), (random()>0))
> > SELECT sum(s),(SELECT sum(s) FROM flips) FROM flips;
> > sum(s),"(SELECT sum(s) FROM flips)"
> > 1,3
> > --Expected output is 1,1.
> >
> > Why isn't the constant notional table table [flips] materialized just
> once
> > per CTE?
> >
> > FYI.  PostgreSQL 9.6 materializes [flips] exactly once per CTE-users
>
> Its documentation says:
> 
> | A useful property of WITH queries is that they are evaluated only once
> | per execution of the parent query, even if they are referred to more
> | than once by the parent query or sibling WITH queries. Thus, expensive
> | calculations that are needed in multiple places can be placed within
> | a WITH query to avoid redundant work. Another possible application is
> | to prevent unwanted multiple evaluations of functions with side-
> | effects. However, the other side of this coin is that the optimizer is
> | less able to push restrictions from the parent query down into a WITH
> | query than an ordinary subquery.
>
> This is an implementation detail of Postgres, and it is not required by
> the SQL specification.  SQLite chose the other side of the coin.
>
>
> Regards,
> Clemens
> ___
> 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] Crash in libsqlite3 with sqlite-3.21.0 and KDE plasmashell

[Leonard Lausen]

Thanks for your prompt reply Richard. With this information I will go
back to the KDE developers and let you know once/if there is further and
detailed evidence for sqlites fault.

Best regards
Leonard

Richard Hipp  writes:

> On 1/19/18, Leonard Lausen  wrote:
>>
>> A crash in sqlite crashes plasmashell. Downstream bug
>> https://bugs.kde.org/show_bug.cgi?id=388140. Please find the backtrace
>> below:
>>
>
> (1) These kinds of things are almost always the result of heap
> corruption in the application.  In other words, there is nothing wrong
> with SQLite.  SQLite just happened to be the unlucky victim to first
> stumble over the corrupted heap.  In fact, we find that whenever there
> is heap corruption of any sort, SQLite is more likely that most other
> libraries to be the first to stumble into it.
>
> (2) You have provided us very little to go on:  There is no version
> number on the SQLite library, no information on the platform on which
> the application is running, no line-number information on the stack
> trace, and no reproducible test case.
>
> (3) Given the above, we will take no action on this.
>
> (4) We will reconsider (3) if you can provide a scintilla of evidence
> that SQLite is at fault.
>
> -- 
> D. Richard Hipp
> d...@sqlite.org

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

Re: [sqlite] Defect: Redundant CTE table materialization gives wrong answer.

[Clemens Ladisch]

petern wrote:
> WITH flips(s) AS (VALUES (random()>0), (random()>0), (random()>0))
> SELECT sum(s),(SELECT sum(s) FROM flips) FROM flips;
> sum(s),"(SELECT sum(s) FROM flips)"
> 1,3
> --Expected output is 1,1.
>
> Why isn't the constant notional table table [flips] materialized just once
> per CTE?
>
> FYI.  PostgreSQL 9.6 materializes [flips] exactly once per CTE-users

Its documentation says:

| A useful property of WITH queries is that they are evaluated only once
| per execution of the parent query, even if they are referred to more
| than once by the parent query or sibling WITH queries. Thus, expensive
| calculations that are needed in multiple places can be placed within
| a WITH query to avoid redundant work. Another possible application is
| to prevent unwanted multiple evaluations of functions with side-
| effects. However, the other side of this coin is that the optimizer is
| less able to push restrictions from the parent query down into a WITH
| query than an ordinary subquery.

This is an implementation detail of Postgres, and it is not required by
the SQL specification.  SQLite chose the other side of the coin.


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