[sqlite] WAL and locking_mode EXCLUSIVE

2013-05-09 Thread Nikolaus Rath
Hello, I'm confused by the effects of WAL on the EXCLUSIVE locking mode. The following works just fine (example uses the apsw Python interface, but it's the same when using the C API): import apsw conn1 = apsw.Connection('barf.db') cur1 = conn1.cursor() cur1.execute('create table foo (bar int)')

Re: [sqlite] Very important performance regression due to totally different query plan

2013-05-09 Thread Stephane MANKOWSKI
Hi Richard, I found this thread (http://www.mail-archive.com/sqlite-users@sqlite.org/msg76831.html) and applied the same correction. Now, I have good performances on all version of sqlite. Thank you for your help. Regards, Stephane Le 08/05/2013 12:14, Stephane MANKOWSKI a écrit : Hi

Re: [sqlite] SQL join with "or" condition gives unexpected results

2013-05-09 Thread Darren Duncan
On 2013.05.09 7:56 AM, Paolo Bolzoni wrote: Seriously? Care to explain? On Thu, May 9, 2013 at 4:48 PM, Petite Abeille wrote: On May 9, 2013, at 3:30 PM, Romulo Ceccon wrote: But my application is (so far) database agnostic Reconsider.

Re: [sqlite] SQL join with "or" condition gives unexpected results

2013-05-09 Thread Paolo Bolzoni
Seriously? Care to explain? On Thu, May 9, 2013 at 4:48 PM, Petite Abeille wrote: > > On May 9, 2013, at 3:30 PM, Romulo Ceccon wrote: > >> But my application is (so far) database agnostic > > Reconsider. Agnosticism is not a feature. It's a

Re: [sqlite] SQL join with "or" condition gives unexpected results

2013-05-09 Thread Petite Abeille
On May 9, 2013, at 3:30 PM, Romulo Ceccon wrote: > But my application is (so far) database agnostic Reconsider. Agnosticism is not a feature. It's a bug. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] SQL join with "or" condition gives unexpected results

2013-05-09 Thread Richard Hipp
A fix is checked in at http://www.sqlite.org/src/info/1128575d0a now. You can download new prerelease snapshots containing the fix from http://www.sqlite.org/draft/download.html or you can manually patch whatever version SQLite you prefer to use by adding the second term of the conditional shown

Re: [sqlite] SQL join with "or" condition gives unexpected results

2013-05-09 Thread Richard Hipp
On Thu, May 9, 2013 at 8:48 AM, Romulo Ceccon wrote: > > The following script demonstrates the problem: > > (test.sql) > CREATE TABLE t(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, > a INTEGER, b INTEGER); > CREATE TABLE mutex(i INTEGER); > INSERT INTO mutex(i)

Re: [sqlite] SQL join with "or" condition gives unexpected results

2013-05-09 Thread Romulo Ceccon
On Thu, May 9, 2013 at 10:22 AM, Simon Slavin wrote: > > Just checking ... you do know that SQLite has both > > INSERT OR ABORT ... > INSERT OR FAIL ... > > statements ? I do. But my application is (so far) database agnostic, and SQLite's behaviour is not being consistent

Re: [sqlite] SQL join with "or" condition gives unexpected results

2013-05-09 Thread Simon Slavin
On 9 May 2013, at 1:48pm, Romulo Ceccon wrote: > I want to use a "mutex" technique to execute an "insert if not > exists"-like statement: Just checking ... you do know that SQLite has both INSERT OR ABORT ... INSERT OR FAIL ... statements ? Simon.

[sqlite] SQL join with "or" condition gives unexpected results

2013-05-09 Thread Romulo Ceccon
I want to use a "mutex" technique to execute an "insert if not exists"-like statement: INSERT INTO t(a, b) SELECT 1, 2 FROM mutex LEFT JOIN t ON a = 1 OR b = 2 WHERE i = 1 AND id IS NULL; However, sqlite3 3.7.16.2 (2013-04-12 11:52:43) is giving me inconsistent results (creates