sqlite3.Cursor.lastrowid isn't populated when executing a SQL REPLACE statement

[jimminter]

BPO	16864
Nosy	@bitdancer, @berkerpeksag
Files	Issue16864_py35.patch sqlite_lastrowid_35.patch sqlite_lastrowid_35_v2.patch sqlite_lastrowid_35_updated.patch sqlite_lastrowid_35_updated_2.patch sqlite_after_review_1.patch sqlite_review_2.patch replace_lastrowid_3_6.patch

^{Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.}

Show more details

GitHub fields:

assignee = None
closed_at = <Date 2016-06-14.12:26:00.807>
created_at = <Date 2013-01-04.17:24:20.292>
labels = ['type-feature', 'library']
title = "sqlite3.Cursor.lastrowid isn't populated when executing a SQL REPLACE statement"
updated_at = <Date 2016-06-14.12:26:00.805>
user = 'https://bugs.python.org/jimminter'

bugs.python.org fields:

activity = <Date 2016-06-14.12:26:00.805>
actor = 'berker.peksag'
assignee = 'ghaering'
closed = True
closed_date = <Date 2016-06-14.12:26:00.807>
closer = 'berker.peksag'
components = ['Library (Lib)']
creation = <Date 2013-01-04.17:24:20.292>
creator = 'jim_minter'
dependencies = []
files = ['34953', '39336', '39339', '39366', '39367', '39411', '39425', '39677']
hgrepos = []
issue_num = 16864
keywords = ['patch']
message_count = 15.0
messages = ['179049', '179058', '216666', '216735', '241137', '242867', '242868', '242882', '243326', '243431', '243442', '243560', '245153', '268553', '268554']
nosy_count = 6.0
nosy_names = ['ghaering', 'r.david.murray', 'python-dev', 'berker.peksag', 'jim_minter', 'Alex.LordThorsen']
pr_nums = []
priority = 'normal'
resolution = 'fixed'
stage = 'resolved'
status = 'closed'
superseder = None
type = 'enhancement'
url = 'https://bugs.python.org/issue16864'
versions = ['Python 3.6']

[jimminter]

sqlite3 doesn't populate the lastrowid member of the Cursor object when a SQL REPLACE statement is executed.

The following snippet doesn't work as I would expect:
cursor = db.execute("REPLACE INTO table(column) VALUES ('datum')")
print cursor.lastrowid # prints None

The following snippet, with SQL which is in effect identical to SQLite, does work as expected:
cursor = db.execute("INSERT OR REPLACE INTO table(column) VALUES ('datum')")
print cursor.lastrowid # prints some rowid

Looking at Modules/_sqlite/cursor.c, in _pysqlite_query_execute(), the following snippet is found:

if (!multiple && statement_type == STATEMENT_INSERT) {
    Py_BEGIN_ALLOW_THREADS
    lastrowid = sqlite3_last_insert_rowid(self->connection->db);
    Py_END_ALLOW_THREADS
    self->lastrowid = PyLong_FromLong((long)lastrowid);
} else {
    Py_INCREF(Py_None);
    self->lastrowid = Py_None;
}

I suggest this should read something like:
if (!multiple && (statement_type == STATEMENT_INSERT || statement_type == STATEMENT_REPLACE)) {
instead of:
if (!multiple && statement_type == STATEMENT_INSERT) {

Thanks,

Jim

[bitdancer]

Considering that sqlite's 'replace' is a synonym for 'insert or replace', I think the logic error is actually in the detect_statement_type function. Since actions are conditionally taken on the REPLACE statement type in the code, including at least one that adjusts the lastrowid, I don't think the fix for lastrowid is as simple as just always setting it. But I'm not that familiar with sqlite internals, so perhaps someone with more knowledge will weigh in.

[AlexLordThorsen]

Have a unit test that replicates this bug. Working on the C code to fix it right now.

[AlexLordThorsen]

Patch that fixes bpo-16864. Follows Jim Minters suggestion.

Unit test will reproduce the issue without the c modifications. C modifications fix the issue.

[bitdancer]

Made some review comments. There also needs to be a documentation change since the docs currently say it is set for insert only. There should be a .. versionchanged directive. This may be small enough not to pass the what's new threshold, but I'd rather add a note and let the person who does the final edit on that document decide, so please add that as well.

(You will note that I've changed this to enhancement...it is documented as *only* working for INSERT, so I believe it must be treated as an enhancement to make it work for REPLACE.)

[AlexLordThorsen]

Updated the patch to have a versionchanged for lastrowid in Doc/Library/sqlite3.rst and Doc/whatsnew/3.5.rst

One thing of note is that I wasn't able to get the indentation to be on the same level for sqlite3.rst (it would either intent the description text or the versionchange text).

Now that I'm actually starting to understand the dbapi and sqlite3 I've come to the conclusion that the lastrowid method should update the lastrowid for the INSERT OR ROLLBACK, INSERT OR ABORT, INSERT OR FAIL, INSERT OR IGNORE statements as well as the INSERT and INSERT OR REPLACE statements.

I'm unsure how hard or simple supporting those statements will be

The code in question is

704 Py_DECREF(self->lastrowid);$
705 if (!multiple && statement_type == STATEMENT_INSERT) {$
706 sqlite_int64 lastrowid;$
707 Py_BEGIN_ALLOW_THREADS$
708 lastrowid = sqlite3_last_insert_rowid(self->connection->db);$
709 Py_END_ALLOW_THREADS$
710 self->lastrowid = _pysqlite_long_from_int64(lastrowid);

And the difficulty will be if sqlite3_last_insert_rowid (line 708) does or doesn't return a row id if the OR STATEMENT portion of those inserts are triggered.

The Problem I'm having is that when I tried to find sqlite3_last_insert_rowid in the Modules/_sqlite directory I got nothing

$ grep -r "sqlite3_last_insert_rowid" Modules/_sqlite/
Modules/_sqlite//cursor.c:            lastrowid = sqlite3_last_insert_rowid(self->connection->db);

When I pulled the grep out to the entire cpython repository

$ grep -r "sqlite3_last_insert_rowid" .
Binary file ./build/lib.macosx-10.10-x86_64-3.5-pydebug/_sqlite3.cpython-35dm-darwin.so matches
Binary file ./build/lib.macosx-10.10-x86_64-3.5-pydebug/_sqlite3.so matches
Binary file ./build/temp.macosx-10.10-x86_64-3.5-pydebug/Users/alexlord/mercurial/cpython/Modules/_sqlite/cursor.o matches
./Modules/_sqlite/cursor.c:            lastrowid = sqlite3_last_insert_rowid(self->connection->db);

I still didn't find anything and I'm not sure where to go from here.

[AlexLordThorsen]

Thanks for Alex_gayner and lifeless. They pointed out the sqlite3_last_row_id is part of the sqlite3 module itself (not cpython).

https://www.sqlite.org/c3ref/last_insert_rowid.html

According the documentation we can expect that if a constraint stops an insertion then the lostrowid is not modified. As such the changes required to add full INSERT OR CONDITION support for last row id is unit tests for each statement and changing the conditional to recognize all insert cases.

[AlexLordThorsen]

Went back to test to see if the other statements are covered already. Unit tests show that lastrowid is set properly no matter what form of sqlite insert statement is used.

sqlite_lastrowid_35_v2.patch contains the doc changes, unit test changes, and code change.

I believe that the latest version of this patch is ready for a code review by a core dev.

[bitdancer]

Added review comments.