DuckDBPyRelation.query(): reusing a virtual table name silently changes the result of an existing relation #604

What happens?

rel.query(virtual_table_name, sql) returns a lazy relation that re-resolves virtual_table_name through the connection's catalog on every execution. Registering the same name again from a different relation therefore changes what an already-constructed relation returns, with no error, even after that relation has been executed once.

Why it matters

Wrong data rather than an error. For a library this rules out any fixed name, so we generate a unique random name per call, which then accumulates.

What I would expect

One of:

1. the derived relation binds the virtual table once, at construction, so it is unaffected by later registrations; or
2. re-registering a name that a live relation depends on raises; or
3. if this is intended, a note on the relational API docs saying that the name is resolved late and must not be reused.

To Reproduce

import duckdb

con = duckdb.connect()
a = con.sql("select 1 as a")
b = con.sql("select 999 as a")

first = a.query("v", "select a from v")
print(first.fetchall()) # [(1,)]

second = b.query("v", "select a from v") # same name, unrelated relation
print(second.fetchall()) # [(999,)]

print(first.fetchall()) # [(999,)] <- silently changed

OS:

Darwin, arm64

DuckDB Package Version:

1.1.0, 1.2.2 and 1.5.5.

Python Version:

3.12

Full Name:

Francesco Bruzzesi

Affiliation:

narwhals-dev

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration to reproduce the issue?

• Yes, I have