Re: [sqlite] 'database disk image is malformed' only on the mac

On 09/15/2017 10:38 AM, Fahad wrote: I tried it with sqlite3_shutdown(), didn't help. I was able to reproduce it again using the main app and the safari share plugin. You don't need to call sqlite3_shutdown(). All it does is release the resources allocated by sqlite3_initialize() - which are

Re: [sqlite] [EXTERNAL] Number of rows in answer set

Are you using SQLite from within an application? Here are some things I do: 1. Put query results into an array and get the count of items in the array. 2. For some tables, store the record count of the table into another table. Then just read that value when you need it. I use triggers to help

Re: [sqlite] Number of rows in answer set

God bless you sir. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf Sent: Monday, September 18, 2017 12:34 PM To: SQLite mailing list Subject: Re: [sqlite] Number of rows in answer set This patch to shell.c will

Re: [sqlite] [EXTERNAL] Number of rows in answer set

>BEGIN IMMEDIATE; >CREATE TEMPORARY TABLE mytemptable >AS >SELECT > INTO temp.mytemptable > FROM > WHERE ; >SELECT count(*) > FROM temp.mytemptable; >SELECT > FROM temp.mytemptable; >DROP TABLE temp.mytemptable; >COMMIT or ROLLBACK; Without the INTO clause of course, as SQLite3 does not

Re: [sqlite] Number of rows in answer set

This patch to shell.c will "fix" the ctrl-c ... This is for an older version of shell.c, so you may have to fiddle to apply the patch to the current version. However, it does work (on Windows). --- shell.c +++ shell.c @@ -794,20 +794,35 @@ if( bSep ){ fprintf(p->out, "%s",

Re: [sqlite] [EXTERNAL] Number of rows in answer set

On Monday, 18 September, 2017 02:53, David Wellman wrote: >" At which point you already know how many rows have been returned." >That's the point, the application doesn't know the exact number >although presumably sqlite does. >Let me explain a bit more. >To run a

Re: [sqlite] Number of rows in answer set

I think I didn't make it clear that I had 2 trains of thought. I'd only expect a total count if I let it run to completion, and not if I killed it. The second part was just a lament that I have to kill the entire session and not just the 1 in-progress query. -Original Message- From:

Re: [sqlite] Number of rows in answer set

On 18 Sep 2017, at 4:31pm, David Raymond wrote: > As far as a use case, something like this would be great for use in the CLI > for example. Sometimes I'll run a query expecting a dozen results only to get > page after page scrolling by. Having something akin to

Re: [sqlite] Number of rows in answer set

As far as a use case, something like this would be great for use in the CLI for example. Sometimes I'll run a query expecting a dozen results only to get page after page scrolling by. Having something akin to "select changes();" that returns an instant answer of how many were selected would be

Re: [sqlite] [EXTERNAL] Number of rows in answer set

On 18 Sep 2017, at 1:59pm, J Decker wrote: > What if the select had an ORDER BY ? wouldn't it have to pull the full set > of rows ? No. Suppose you declare an index on the same sort order. Then SQLite could just read the rows starting from the top of the index. CREATE

Re: [sqlite] [EXTERNAL] Number of rows in answer set

On 9/18/17, J Decker wrote: > What if the select had an ORDER BY ? wouldn't it have to pull the full set > of rows ? Not necessarily. The query planner works hard to try to get the rows to come out naturally in the correct order, without sorting, as doing so makes the query

Re: [sqlite] *** suspected spam or bulk *** Re: [EXTERNAL] Number of rows in answer set

This was already addressed. If the ORDER BY clause cannot be fulfilled by virtue of the query plan, the full result set must be retrieved and sorted before even the first row can be returned. This is expensive (memory and/or IO bandwidth) and makes the query seem unresponsive and therefore

Re: [sqlite] [EXTERNAL] Number of rows in answer set

What if the select had an ORDER BY ? wouldn't it have to pull the full set of rows ? On Mon, Sep 18, 2017 at 5:11 AM, Simon Slavin wrote: > > > On 18 Sep 2017, at 10:41am, David Wellman > wrote: > > > So to answer my original question: there

Re: [sqlite] [EXTERNAL] Number of rows in answer set

On 18 Sep 2017, at 10:41am, David Wellman wrote: > So to answer my original question: there isn't an api that gives this value > ** because ** SQLite doesn't build the full answer set before returning from > that first sqlite3_step function call. Correct.

Re: [sqlite] [EXTERNAL] Number of rows in answer set

On Mon, Sep 18, 2017 at 11:41 AM, David Wellman wrote: > [...] there isn't an api that gives this value ** because ** SQLite > doesn't build the full answer set before returning from that first > sqlite3_step function call. > [DD] Well, the answer is more that

Re: [sqlite] [EXTERNAL] Number of rows in answer set

First, thanks to everyone for their input on this. It has resulted in a much bigger discussion than I had assumed would happen... I obviously have a fundamental misunderstanding of how SQLite processes a request, (I'll read the link that Hick gave me - thanks for that). So to answer my

Re: [sqlite] [EXTERNAL] Number of rows in answer set

My point was it's not super fast though -- rather than being an O(1) lookup it uses I/O and compute proportional to the number of rows in the table (which I first noticed when a SELECT count(*) was taking minutes on a 50GB database). I'm not complaining, and I'm aware you can use triggers to

Re: [sqlite] [EXTERNAL] Number of rows in answer set

The reason "select count(*) from t" is super fast is the special "count" opcode that does the "running" in just one go, instead of calling "Column", "AggStep" and "Next" in a loop. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im

Re: [sqlite] [EXTERNAL] Number of rows in answer set

This is documented at https://sqlite.org/eqp.html#the_explain_query_plan_command, complete with the caveat that it is for interactive debugging only and subject to change without notice. Some releases would output the estimated number of rows, and the virtual table interface requires the

Re: [sqlite] [EXTERNAL] Number of rows in answer set

On 18 September 2017 at 16:52, David Wellman wrote: > To run a select statement the application code has to: > sqlite3_prepare: parse the sql, make sure it's valid, build the plan > (using "nifty heuristics" :-) ) > sqlite3_step - this one runs the sql, builds an

Re: [sqlite] [EXTERNAL] Number of rows in answer set

You presumtion is wrong. SQLite does not know in advance how many rows are in the result set until it actually goes looking for them. Calling sqlite3_step() instructs SQLite to go look for the next result row and return as soon as it has found it, so that the application can do something useful

Re: [sqlite] [EXTERNAL] Number of rows in answer set

On 9/18/17, David Wellman wrote: > sqlite3_step - this one runs the sql, builds an answer set and then returns > the first row No. sqlite3_step() does not "build the answer set". It only begins computing the answer, stopping at the first row. The sqlite3_step()

Re: [sqlite] [EXTERNAL] Number of rows in answer set

Hi Hick, " The only way to come up with the exact number of result rows is to actually run the query." - agreed " At which point you already know how many rows have been returned." That's the point, the application doesn't know the exact number although presumably sqlite does. Let me

Re: [sqlite] [EXTERNAL] Number of rows in answer set

On Mon, Sep 18, 2017 at 10:37 AM, Hick Gunter wrote: > SQLite uses some nifty heuristics to estimate the number of rows it > expects to process while formulating a query plan. [...] > Is there any way to get at that estimate? That would be interesting to pre-size some result

Re: [sqlite] [EXTERNAL] Number of rows in answer set

SQLite uses some nifty heuristics to estimate the number of rows it expects to process while formulating a query plan. The only way to come up with the exact number of result rows is to actually run the query. At which point you already know how many rows have been returned. -Ursprüngliche

[sqlite] Number of rows in answer set

Hi, If I run a SELECT statement that returns an answer set is there an api call that will tell me "number of rows in answer set" - something like "sqlite3_row_count"? I have looked at the calls but couldn't find any - sorry if I've missed it. I know could use a "create temporary table xxx as