[New-bugs-announce] [issue36859] sqlite3 dml statement detection does not account for CTEs

[Charles]

New submission from Charles <coleifer at gmail.com>:

In statement.c, there is some logic which detects whether or not an incoming statement is a DML-type. The logic, as of 2019-05-08, I am referring to is here: https://github.com/python/cpython/blob/fc662ac332443a316a120fa5287c235dc4f8739b/Modules/_sqlite/statement.c#L78-L93

To demonstrate the bug:

import sqlite3

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

conn.execute('create table kv ("key" text primary key, "value" integer)')
conn.execute('insert into kv (key, value) values (?, ?), (?, ?)',
             ('k1', 1, 'k2', 2))

assert conn.in_transaction  # Yes we are in a transaction.
conn.commit()
assert not conn.in_transaction  # Not anymore, as expected.

rc = conn.execute(
    'with c(k, v) as (select key, value + 10 from kv) '
    'update kv set value=(select v from c where k=kv.key)')

print(rc.rowcount)  # Should be 2, prints "-1".
#assert conn.in_transaction  # !!! Fails.

curs = conn.execute('select * from kv order by key;')
print(curs.fetchall())  # [('k1', 11), ('k2', 12)]

----------
components: Library (Lib)
messages: 341949
nosy: coleifer
priority: normal
severity: normal
status: open
title: sqlite3 dml statement detection does not account for CTEs
type: behavior

_______________________________________
Python tracker <report at bugs.python.org>
<https://bugs.python.org/issue36859>
_______________________________________