Title: sqlite3 parameter substitution breaks with multiple parameters
Type: Stage:
Components: Versions: Python 2.6
Status: closed Resolution: not a bug
Dependencies: Superseder:
Assigned To: Nosy List: angri, marcin.bachry, simon.jagoe, terry.reedy
Priority: normal Keywords:

Created on 2010-05-11 16:27 by simon.jagoe, last changed 2010-05-14 21:16 by terry.reedy. This issue is now closed.

Messages (8)
msg105525 - (view) Author: Simon Jagoe (simon.jagoe) Date: 2010-05-11 16:27
I have been using sqlalchemy and sqlamp in a project for a while with Python 2.5.x and Python 2.6.4. With a recent upgrade to Python 2.6.5 (on Ubuntu Lucid Lynx), a particular operation began to fail when using sqlite.

I have tracked this to using the sqlite3 module and multiple parameter substitution. See below for a simple test case.

Set up the database:

 >>> import sqlite3
 >>> conn = sqlite3.connect('test.sqlite3')
 >>> c = conn.cursor()
 >>> c.execute('create table test (col integer)')
 >>> c.execute('insert into test values (1)')
 >>> conn.commit()

Actual result:

 >>> c.execute('SELECT coalesce(max(test.col), ?) + ? AS col FROM test', (0, 1))
 >>> c.fetchone()

Expected result:

 >>> c.execute('SELECT coalesce(max(test.col), ?) + ? AS col FROM test', (0, 1))
 >>> c.fetchone()

The expected result can be simulated like this:

 >>> c.execute('SELECT coalesce(max(test.col), 0) + 1 AS col FROM test')
 >>> c.fetchone()
msg105654 - (view) Author: Marcin Bachry (marcin.bachry) Date: 2010-05-13 20:29
I wrote a Perl script testing this issue and it fails too, so it's most probably a bug in sqlite itself.  I think the bug is already reported under id 26ff0c82d1 in sqlite bug tracker:
msg105744 - (view) Author: Terry J. Reedy (terry.reedy) * (Python committer) Date: 2010-05-14 19:22
You appear to be saying that the same code gives different results on 2.6.4 and 2.6.5. Correct? If so, did the version of sqlite change?

In any case, 3.1.2 on WinXP consistently gives (2,)
>>> sqlite3.version
msg105745 - (view) Author: Simon Jagoe (simon.jagoe) Date: 2010-05-14 19:27
I will try to test this with the different combinations of python and sqlite versions.
msg105751 - (view) Author: angri (angri) Date: 2010-05-14 19:48
This thread contains some observations of versions affected:
msg105758 - (view) Author: Terry J. Reedy (terry.reedy) * (Python committer) Date: 2010-05-14 20:31
The next question is whether there is any bug in the Python interface module or whether c.execute(s,t) is properly passing s and t to sqlite3 for processing and converting the return to a Python tuple. If the latter, then this should be closed as invalid (because it is a 3rd party problem).
msg105761 - (view) Author: Simon Jagoe (simon.jagoe) Date: 2010-05-14 20:46
Sorry for the waste of time. I have compiled Python 2.6.5 against sqlite3 3.6.22 (Lucid version) and 3.6.16-1ubuntu1 (Karmic version).

The test I posted originally fails with sqlite3 3.6.22 and passes with sqlite3 3.6.16-1ubuntu1, so it would appear to be a change in the library that is causing this error.
msg105763 - (view) Author: Terry J. Reedy (terry.reedy) * (Python committer) Date: 2010-05-14 21:16
Given that this is a real, and disconcerting problem, and not a failure to read the doc, no apology needed. At least one solution is recorded here for anyone else hitting the same problem.

If anyone thinks there should be a change to the sqlite3 module, they can reopen this.
Date User Action Args
2010-05-14 21:16:58terry.reedysetstatus: open -> closed
resolution: not a bug
messages: + msg105763
2010-05-14 20:46:10simon.jagoesetmessages: + msg105761
2010-05-14 20:31:49terry.reedysetmessages: + msg105758
2010-05-14 19:48:56angrisetmessages: + msg105751
2010-05-14 19:27:07simon.jagoesetmessages: + msg105745
2010-05-14 19:22:57terry.reedysetnosy: + terry.reedy
messages: + msg105744
2010-05-13 20:29:55marcin.bachrysetnosy: + marcin.bachry
messages: + msg105654
2010-05-11 19:54:29angrisetnosy: + angri
2010-05-11 16:27:00simon.jagoecreate