[issue23129] sqlite3 COMMIT nested in SELECT returns unexpected results

[Berker Peksag]

Berker Peksag added the comment:

Thanks for the report! This is a duplicate of issue 10513. I've pushed a patch 
to fix this for 2.7 and 3.5+ in 030e100f048a and 81f614dd8136.

--
components: +Extension Modules
nosy: +berker.peksag
resolution:  -> duplicate
stage:  -> resolved
status: open -> closed
superseder:  -> sqlite3.InterfaceError after commit
versions: +Python 3.5, Python 3.6

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue23129] sqlite3 COMMIT nested in SELECT returns unexpected results

[Daniel Lenski]

Daniel Lenski added the comment:

I agree on the nastiness of this bug. It's been plaguing my production code for 
months and I had been at a loss to explain why I've been getting duplicate rows 
until I found this SO post:

http://stackoverflow.com/questions/27624049/python-sqlite3-cursor-returns-duplicates-when-a-commit-intervenes

By the way, the test case in Jim Carroll's report appears to come from this 
StackOverflow question.

--
nosy: +dlenski

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue23129] sqlite3 COMMIT nested in SELECT returns unexpected results

[Gian-Carlo Pascutto]

Gian-Carlo Pascutto added the comment:

I've ran into this as well, when a program that was running correctly with 
PostgreSQL turned out to produce garbage with SQLite. Code to reproduce roughly 
looks like this:

sel_cursor = conn.cursor()
sel_cursor.execute(SELECT prim_key_id FROM 
   somedb ORDER BY start_time ASC)
add_cursor = conn.cursor()
prim_keys = set()
row = sel_cursor.fetchone()
while row:
seq = row[0]
if seq in prim_keys:
raise RuntimeError
prim_keys.add(seq)
add_cursor.execute(INSERT INTO someotherdb 
   VALUES (?),
   seq)
conn.commit()
row = sel_cursor.fetchone()
conn.commit()

This will raise a RuntimeError because the SELECT will return the same primary 
key value twice - something that's obviously impossible.

This exact bug has been filed once more already, and it seems to be an actual 
regression in Python 2.7:
http://bugs.python.org/issue10513

Looking at the code there, I agree with the analysis in this message: 
http://bugs.python.org/issue10513#msg150162

Either the pysqlite_do_all_statements should set reset_cursors=1 to warn the 
user that the cursor has been reset, or it shouldn't reset them to begin with 
(as Python 2.7 used to do).

I don't think there's any argument this isn't a (bad) bug: Python 2.7 works 
correctly and later versions silently corrupt data.

--
nosy: +Gian-Carlo Pascutto

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue23129
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue23129] sqlite3 COMMIT nested in SELECT returns unexpected results

[R. David Murray]

R. David Murray added the comment:

(Please trim the original message when replying to a tracker issue.)

When I said, that probably means it's a doc issue, I meant that, even if it 
is a bug, for backward compatibility reasons we'd have to just document the 
behavior.  Fixing the bug would then be a new feature (new keyword to change 
the behavior or some such).  (ie: the fix to a design bug is a feature :)

Now, if it turns out that the bug lies elsewhere, or that changing the current 
statement-reset behavior wouldn't actually change the meaning of anyone's 
program if we changed it, then we could fix it as a bug.  But we'll need to 
understand the bug in detail before we can make that determination.

--

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue23129
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue23129] sqlite3 COMMIT nested in SELECT returns unexpected results

[Jim Carroll]

Jim Carroll added the comment:

Completely understood.

I recently found a workaround. Setting isolation_level to None seems to 
mitigate the issue, ie:

db = sq.connect(':memory:', isolation_level=None)

I'm hoping to put some time in scrutinizing the c-api code later this week (as 
SQLite bugs directly affect projects we work on) to see if we can get to the 
bottom of the issue.

--
title: sqlite3 COMMIT nested in SELECT returns unexpected   results - 
sqlite3 COMMIT nested in SELECT returns unexpected results

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue23129
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue23129] sqlite3 COMMIT nested in SELECT returns unexpected results

[R. David Murray]

R. David Murray added the comment:

Ah, then I suspect you are getting hit by the 'automatic transaction' feature 
of the DB2 API.  So it is probably not the commit, but the subsequent implicit 
'begin transaction' that is causing the problem.  The trick then is to figure 
out why that affects the existing select cursor.

--

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue23129
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue23129] sqlite3 COMMIT nested in SELECT returns unexpected results

[Jim Carroll]

New submission from Jim Carroll:

I reported this to the sqlite mailing list, and the comments I received 
suggested the problem may by the python sqlite connector issue, so I'm opening 
this as a bug report.

I understand that performing a SELECT and nested COMMIT on the same table is 
not supported in sqlite, but I would have expected a COMMIT on a separate table 
would not be a problem.  Some test code in python however reveals that 
performing the COMMIT disrupts the SELECT statement, and causes duplicate data 
to be returned.

If this is not a supported operation, would you mind pointing me to the docs so 
I can understand it better?

Example

#!/usr/bin/env python

import sqlite3 as sq

db = sq.connect(':memory:')

db.execute('CREATE TABLE tbl (col INTEGER)')
db.execute('CREATE TABLE tbl2 (col INTEGER)')
db.executemany('INSERT INTO tbl (col) VALUES (?)', [(0,), (1,), (2,)])
db.commit()

print('count=' + str(db.execute('SELECT count(*) FROM tbl').fetchone()[0]))

# Read and print the values just inserted into tbl
for col in db.execute('SELECT col FROM tbl'):
print(col)
db.execute('INSERT INTO tbl2 VALUES (?)', col)
db.commit()

print('count=' + str(db.execute('SELECT count(*) FROM tbl').fetchone()[0]))


The output we see is:

count=3
(0,)
(1,)
(0,)
(1,)
(2,)
count=3


The expected output was:

count=3
(0,)
(1,)
(2,)
count=3

Tested on Linux:

sqlite version 3.7.13

sqlite3 connector version 2.6.0
 
# uname -a
Linux hostname 3.16-0.bpo.3-amd64 #1 SMP Debian 
3.16.5-1~bpo70+1 (2014-11-02) x86_64 GNU/Linux

Also tested on Windows with identical results

 Sqlite version 3.6.21
 Windows 7 Professional, 64-bit

--
messages: 233178
nosy: jamercee
priority: normal
severity: normal
status: open
title: sqlite3 COMMIT nested in SELECT returns unexpected results
type: behavior
versions: Python 2.7

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue23129
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue23129] sqlite3 COMMIT nested in SELECT returns unexpected results

[R. David Murray]

R. David Murray added the comment:

I'd say you have a bug here of some sort, but I'm not sure if it is a doc bug 
or a code bug.  Commit specifically does *not* reset the cursors, according to 
the code, but I'm not even sure what resetting a cursor means :)  I've poked 
around the sqlite3 module's code a bit, but not enough to have an answer to 
this.  I do note that a commit does a call to sqlite3_reset on all statements 
associated with the connection, and I suspect that's where the problem 
originates.  Which probably makes it a doc bug.

--
nosy: +r.david.murray

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue23129
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue23129] sqlite3 COMMIT nested in SELECT returns unexpected results

[Jim Carroll]

Jim Carroll added the comment:

Hi David,

One more data point. Although I demonstrated the bug using the .execute() 
method associated with a connection object -- you can also create the exact 
problem using the .execute() method associated with cursors. This leaves no 
means to COMMIT inside a nested SELECT.

The members of the sqlite mailing list confirmed they had no problem executing 
the SQL statements using C and PHP. I think this is a bug, rather than just a 
problem with the docs.

I've been digging around the pysqlite C source but can't quite figure out 
what's going on yet.

Jim

 -Original Message-
 From: R. David Murray [mailto:rep...@bugs.python.org]
 Sent: Monday, December 29, 2014 1:08 PM
 To: j...@carroll.com
 Subject: [issue23129] sqlite3 COMMIT nested in SELECT returns
 unexpected results


 R. David Murray added the comment:

 I'd say you have a bug here of some sort, but I'm not sure if it is a
 doc bug or a code bug.  Commit specifically does *not* reset the
 cursors, according to the code, but I'm not even sure what resetting a
 cursor means :)  I've poked around the sqlite3 module's code a bit, but
 not enough to have an answer to this.  I do note that a commit does a
 call to sqlite3_reset on all statements associated with the connection,
 and I suspect that's where the problem originates.  Which probably
 makes it a doc bug.

 --
 nosy: +r.david.murray

 ___
 Python tracker rep...@bugs.python.org
 http://bugs.python.org/issue23129
 ___

--
title: sqlite3 COMMIT nested in SELECT returns unexpected results - sqlite3 
COMMIT nested in SELECT returns unexpected results

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue23129
___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com