SQLite rowcount is corrupted when combining UPDATE RETURNING w/ id IN (?)

[rt121212121]

Bug report

The rowcount from an sqlite UPDATE RETURNING query with WHERE id IN (?) used (but not == ?) will be 0.

The reproduction case from #93421 runs successfully. The modified reproduction case below errors with:

Traceback (most recent call last):
  File "C:\Data\R\git\electrumsv\x.py", line 41, in <module>
    go()
  File "C:\Data\R\git\electrumsv\x.py", line 35, in go
    assert rowcount == 1, (rowcount, f"was updated: {updated_value=='v2'}")
AssertionError: (0, 'was updated: True')

Reproduction case.

import os
import sqlite3



def go():
    # create table
    cursor = conn.cursor()
    cursor.execute(
        """CREATE TABLE some_table (
        id INTEGER NOT NULL,
        flags INTEGER NOT NULL,
        value VARCHAR(40) NOT NULL,
        PRIMARY KEY (id)
    )
    """
    )
    cursor.close()
    conn.commit()

    # run operation
    cursor = conn.cursor()
    cursor.execute(
        "INSERT INTO some_table (id, flags, value) VALUES (1, 4, 'v1')"
    )
    ident = 1

    cursor.execute(
        "UPDATE some_table SET value='v2' "
        "WHERE id IN (?) RETURNING id",
        (ident,),
    )
    rowcount = cursor.rowcount
    updated_value = cursor.execute("SELECT value FROM some_table WHERE id=1").fetchone()[0]
    assert rowcount == 1, (rowcount, f"was updated: {updated_value=='v2'}")

if os.path.exists("file.db"):
    os.unlink("file.db")

conn = sqlite3.connect("file.db")
go()
# never reaches here

Your environment

This is the 3.10.9 windows install from python.org on my computer. My coworker installed the version on his Windows computer and could reproduce it as well.

Python 3.10.9 (tags/v3.10.9:1dd9be6, Dec  6 2022, 20:01:21) [MSC v.1934 64 bit (AMD64)] on win32

My coworker has also had it happen on MacOS with 3.10.8

3.10.8 (main, Oct 13 2022, 10:17:43) [Clang 14.0.0 (clang-1400.0.29.102)]

3.2 GHz 8-Core Intel Xeon W

Linked PRs

• gh-101117: Improve accuracy of sqlite3.Cursor.rowcount docs #104287
• [3.11] gh-101117: Improve accuracy of sqlite3.Cursor.rowcount docs (#104287) #104381

[rt121212121]

This is reproducible with the Windows builds for 3.10.9, 3.10.8, 3.10.7, 3.10.6.
This is not reproducible with the Windows build for 3.10.5.

[rt121212121]

Coincidentally the only sqlite related fix in 3.10.6 in the changelog is for #93421.

[erlend-aasland]

A UPDATE ... RETURNING query returns resulting rows. Example use:

query = "UPDATE some_table SET value='v2' WHERE id IN (?) RETURNING id"
params = (1,)
for row in cursor.execute(query, params):
    print("row", row, "updated")

The underlying SQLite library will only update the row count for every step that returns a row. In your example, you do not fetch all resulting rows; the UPDATE ... RETURNING statement is still running when you execute the SELECT query, effectively aborting the UPDATE ... RETURNING statement.

[rt121212121]

Just as a last note for possible later reference should the circumstances around this change, I would note that one confounder I encountered was that on Azure Pipelines, we use 3.10.9 with the unfixed larger test case that is failing in 3.10.6 and above on Windows, and it passes every time. What does this mean? I do not know.

[erlend-aasland]

Hm, yes that is a little bit weird. I'm reopening this until I've found time to investigate it again.

[erlend-aasland]

Sorry 'bout the delay; I got lost in other issues.

The issue here, is that you're executing a query that returns row, but you do not let that query complete (you do not collect the resulting rows, as expected by an UPDATE...RETURNING query). The behaviour SQLite C API sqlite3_changes is unpredictable for statements that has not been completed (returned SQLITE_DONE). If you collect the result of the UPDATE..RETURNING query, you'll get the expected result. Alternatively, you can remove the RETURNING clause, since you're not using it.

AFAICS, this is misuse of the API: if you supply an UPDATE...RETURNING query, make sure you run it to completion before checking the row count. Perhaps a docs update could make this clearer.