SQLite rowcount is corrupted when combining UPDATE RETURNING w/ table that is dropped and recreated

version info:

$ python
Python 3.10.0 (default, Nov  5 2021, 17:23:47) [GCC 11.2.1 20210728 (Red Hat 11.2.1-1)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.sqlite_version
'3.36.0'

we have a test suite that creates a table, runs some SQL, then drops it. if multiple tests run that each perform this task, if the same SQLite connection is used, rowcount starts returning "0". Seems to also require RETURNING to be used. Full demonstration:

import os
import sqlite3



def go():
    """function creates a new table, runs INSERT/UPDATE, drops table,
    commits connection.

    """

    # create table
    cursor = conn.cursor()
    cursor.execute(
        """CREATE TABLE some_table (
        id 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, value) VALUES (1, 'v1')"
    )
    ident = 1

    cursor.execute(
        "UPDATE some_table SET value='v2' "
        "WHERE id=? RETURNING id",
        (ident,),
    )
    new_ident = cursor.fetchone()[0]
    assert ident == new_ident
    assert cursor.rowcount == 1, cursor.rowcount
    cursor.close()

    # drop table
    cursor = conn.cursor()
    cursor.execute("DROP TABLE some_table")
    cursor.close()

    conn.commit()

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

# passes
conn = sqlite3.connect("file.db")
go()

# run again w/ new connection (same DB), passes
conn = sqlite3.connect("file.db")
go()

print("FAILURE NOW OCCURS")
# run again w/ same connection, fails
go()

on the third run, where we ran the "test" on the same connection twice, it fails:

$  python test3.py 
FAILURE NOW OCCURS
Traceback (most recent call last):
  File "/home/classic/dev/sqlalchemy/test3.py", line 62, in <module>
    go()
  File "/home/classic/dev/sqlalchemy/test3.py", line 39, in go
    assert cursor.rowcount == 1, cursor.rowcount
AssertionError: 0

it would appear there's some internal caching of table state that needs to be cleared.

@erlend-aasland

cc: @erlend-aasland

A

Thanks for the report!

Internally, we simply use the function sqlite3_changes to set rowcount¹, implying that we do not calculate rowcount ourself; the value comes straight from the underlying SQLite library. If sqlite3_changes returns zero, rowcount will be zero.

I've verified that this only happens with UPDATE ... RETURNING statements, which also implies that the behaviour we are observing comes from the underlying SQLite library. I suggest to report this on the SQLite forum (they do not have a bug tracker).

Note: I did some tests where I used sqlite3_total_changes() in addition to sqlite3_changes(), and they revealed that the number of total changes on the database connection did not increase whith UPDATE ... RETURNING statements, only with normal UPDATE statements.

Closing this as not-a-bug.

Navigation Menu

Repository navigation

SQLite rowcount is corrupted when combining UPDATE RETURNING w/ table that is dropped and recreated #93421

25 remaining items

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

SQLite rowcount is corrupted when combining UPDATE RETURNING w/ table that is dropped and recreated #93421

Description

Activity

AA-Turner commented on Jun 1, 2022

erlend-aasland commented on Jun 2, 2022

Footnotes

25 remaining items

erlend-aasland commented on Jun 8, 2022

erlend-aasland commented on Jun 8, 2022

erlend-aasland commented on Jun 8, 2022

rt121212121 commented on Dec 1, 2022

erlend-aasland commented on Dec 2, 2022

rt121212121 commented on Dec 5, 2022

rt121212121 commented on Jan 18, 2023

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions