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

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

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

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

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

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.

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

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

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

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

[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

Re: [sqlite] WAL and pragma uncommitted

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

Re: [sqlite] WAL and pragma uncommitted

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

Re: [sqlite] WAL and pragma uncommitted

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

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

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

Re: [sqlite] WAL and pragma uncommitted

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 ?

Re: [sqlite] WAL and pragma uncommitted

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

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

[sqlite] WAL and pragma uncommitted

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

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

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.

[sqlite] About test_vfs.c in sqlite test

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

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

[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; //

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

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

Re: [sqlite] Crash in libsqlite3 with sqlite-3.21.0 and KDE plasmashell

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

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

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