[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 <report at bugs.python.org>
<http://bugs.python.org/issue23129>
_______________________________________