classification
Title: SQLite incorrect row count for UPDATE
Type: Stage: patch review
Components: Library (Lib) Versions: Python 3.8, Python 3.7, Python 3.6, Python 2.7
process
Status: open Resolution:
Dependencies: Superseder:
Assigned To: Nosy List: Montana Low, ghaering, lys.nikolaou, xtreak, zzzeek
Priority: normal Keywords: patch

Created on 2018-12-04 02:27 by Montana Low, last changed 2018-12-05 03:53 by Montana Low.

Pull Requests
URL Status Linked Edit
PR 10913 open python-dev, 2018-12-05 03:48
Messages (3)
msg331006 - (view) Author: Montana Low (Montana Low) * Date: 2018-12-04 02:27
SQLite driver returns an incorrect row count (-1) for UPDATE statements that begin with a comment.

Downstream Reference:
https://github.com/sqlalchemy/sqlalchemy/issues/4396


Test Case:

```
import sqlite3

conn = sqlite3.connect(":memory:")

cursor = conn.cursor()

cursor.execute("""
CREATE TABLE foo (
    id INTEGER NOT NULL,
    updated_at DATETIME,
    PRIMARY KEY (id)
)
""")

cursor.execute("""
/* watermarking bug */
INSERT INTO foo (id, updated_at) VALUES (?, ?)
""", [1, None])


cursor.execute("""
    UPDATE foo SET updated_at=? WHERE foo.id = ?
""", ('2018-12-02 14:55:57.169785', 1))

assert cursor.rowcount == 1

cursor.execute("""
    /* watermarking bug */
    UPDATE foo SET updated_at=? WHERE foo.id = ?
""", ('2018-12-03 14:55:57.169785', 1))

assert cursor.rowcount == 1
```
msg331069 - (view) Author: Karthikeyan Singaravelan (xtreak) * (Python triager) Date: 2018-12-04 17:47
Did some debugging here. If I am understanding this correctly the rowcount is set at https://github.com/python/cpython/blob/b8e689a6e8134e88f857a55e50b6a4977967e385/Modules/_sqlite/cursor.c#L574 . It checks for is_dml flag that is set here https://github.com/python/cpython/blob/b8e689a6e8134e88f857a55e50b6a4977967e385/Modules/_sqlite/statement.c#L78

The part to set is_dml skips space, tabs and newline and checks for the first set of characters that is not skipped to be insert, update, delete or replace and in this case the first set of characters to be matched will be "/* watermarking */". Thus with comment not matching, is_dml is not set and -1 is set for the rowcount.
msg331090 - (view) Author: Montana Low (Montana Low) * Date: 2018-12-05 03:53
I took a stab at patch. It fixes the issue for me, as proven via the Test Case.
History
Date User Action Args
2018-12-05 03:53:10Montana Lowsetmessages: + msg331090
2018-12-05 03:48:51python-devsetkeywords: + patch
stage: patch review
pull_requests: + pull_request10152
2018-12-04 17:47:39xtreaksetmessages: + msg331069
versions: + Python 3.7, Python 3.8
2018-12-04 17:35:36lys.nikolaousetnosy: + lys.nikolaou
2018-12-04 17:31:47xtreaksetnosy: + ghaering, xtreak
2018-12-04 03:34:43zzzeeksetnosy: + zzzeek
2018-12-04 02:27:45Montana Lowcreate