Re: [sqlite] Handling ROLLBACK

2019-03-04 Thread Keith Medcalf
On Monday, 4 March, 2019 20:23, Rowan Worth wrote: >On Sun, 3 Mar 2019 at 20:53, Keith Medcalf wrote: >> Statements which were in progress that were permitted to proceed >> (ie, where the next step did not return an abort error) continue >> with a read lock in place (ie, as if they were part

Re: [sqlite] Handling ROLLBACK

2019-03-04 Thread Rowan Worth
On Sun, 3 Mar 2019 at 20:53, Keith Medcalf wrote: > My observation (on the current tip version 3.28.0) of Schrodingers > Transactions is that if there is (for example) a transaction in progress > and that is COMMIT or ROLLBACK, then the changes are either committed or > rolled back and the

Re: [sqlite] Equiv stmts, different explain plans

2019-03-04 Thread Keith Medcalf
In the first query the subselect that creates the list is independent. In the second query the subselect that creates the list is correlated. In the first query you have requested that the subquery be executed to create the list for use by the IN operator. After this has been done the main

Re: [sqlite] Deserialize a WAL database file

2019-03-04 Thread Rowan Worth
On Fri, 1 Mar 2019 at 18:26, Lloyd wrote: > I have two database files. One in Rollback mode and the other in WAL mode. > I am able to serialize, deserialize and prepare a SQL query against the > rollback database. When I do the same against the WAL database file, the > 'prepare' statement fails

Re: [sqlite] Equiv stmts, different explain plans

2019-03-04 Thread kk
On 05/03/2019 01:33, Richard Hipp wrote: On 3/4/19, Kyle wrote: On another DB I came across 2 stmts, that I think are equivalent, but generated different explain plans. I request a second opinion - are these 2 stmts equivalent? If so, why do they generate different explain plans even on

Re: [sqlite] Equiv stmts, different explain plans

2019-03-04 Thread Richard Hipp
On 3/4/19, Kyle wrote: > On another DB I came across 2 stmts, that I think are equivalent, but > generated different explain plans. I request a second opinion - are > these 2 stmts equivalent? If so, why do they generate different explain > plans even on sqlite? The two SELECT statements below

[sqlite] Equiv stmts, different explain plans

2019-03-04 Thread Kyle
On another DB I came across 2 stmts, that I think are equivalent, but generated different explain plans. I request a second opinion - are these 2 stmts equivalent? If so, why do they generate different explain plans even on sqlite? TIA -- create table t1(c,d); create table t2(c,d); explain

Re: [sqlite] Is there a tool to convert `where`s to equivalent `join`s?

2019-03-04 Thread James K. Lowden
On Fri, 1 Mar 2019 13:44:57 +0530 Rocky Ji wrote: > So for learning sake, is there a tool that converts a query using > `WHERE` to a query (that yields identical results) using JOINs? Like > a English -> side-by-side translator. SQL-92 was introduced a long time ago. At that time, I don't