[Bug]: calling .commit causes results to be empty

[edublancas]

What happened?

I noticed that calling .commit() on a connection makes existing results empty, this differs from duckdb's native behavior. Is this a bug or some expected behavior?

For context: in JupySQL, we have an autocommit mode that automatically .commit() after executing some SQL query, and I discovered this after debugging some bug reports.

Here's how to reproduce the issue:

import duckdb
duckdb.__version__

'0.8.1'

import duckdb_engine
duckdb_engine.__version__

'0.9.1'

import sqlalchemy
sqlalchemy.__version__

'2.0.19'

conn_native = duckdb.connect()
engine = sqlalchemy.create_engine("duckdb://")
conn_engine = engine.connect()

conn_native.execute("create table my_table (x INT)")
conn_native.execute("insert into my_table (x) VALUES (1), (2), (3)")
results = conn_native.execute("select * from my_table")
conn_native.commit()
print(results.fetchall())

[(1,), (2,), (3,)]

conn_engine.execute(sqlalchemy.text("create table my_table (x INT)"))
conn_engine.execute(sqlalchemy.text("insert into my_table (x) VALUES (1), (2), (3)"))
results = conn_engine.execute(sqlalchemy.text("select * from my_table"))
conn_engine.commit()
print(results.fetchall())

[]

DuckDB Engine Version

0.9.1

DuckDB Version

0.8.1

SQLAlchemy Version

2.0.19

Relevant log output

No response

Code of Conduct

• I agree to follow this project's Code of Conduct

[Mause]

Hey there, the native commit method is a noop if you don't already have a transaction open. If you try the native version with an open transaction, it returns an empty result as well.

Does that help?

[edublancas]

oh, right! I was able to reproduce this behavior by calling .begin() using the native connection, thanks!