bug: fetch_rel_time_zone fails with read-only MotherDuck connection

[spark-dataduck]

Description

fetch_rel_time_zone() in narwhals/_duckdb/utils.py:207 fails when the DuckDB relation is connected to MotherDuck via a read-only token. The error occurs because rel.query() internally creates a temporary view in the relation's database context, which is a write operation that fails on a read-only database.

This affects any Narwhals operation that touches the schema of a DuckDB relation containing a TIMESTAMPTZ column — including collect_schema(), filtering, selecting, joining, etc.

Error

Invalid Input Error: Failed to create view 'duckdb_settings()':
Cannot execute statement of type "RELATION" on database "my_db" which is attached in read-only mode!

Root Cause

The current implementation at utils.py:207-212:

def fetch_rel_time_zone(rel: duckdb.DuckDBPyRelation) -> str:
    result = rel.query(
        "duckdb_settings()", "select value from duckdb_settings() where name = 'TimeZone'"
    ).fetchone()
    assert result is not None
    return result[0]

rel.query(view_name, sql) works by:

1. Creating a temporary view (named "duckdb_settings()") inside the relation's database — this is a write operation
2. Running the SQL query
3. Dropping the view

When the relation belongs to a read-only MotherDuck database, step 1 fails.

Note: calling conn.sql("SELECT value FROM duckdb_settings() WHERE name = 'TimeZone'") directly on the connection works fine — the issue is specifically rel.query().

Call Chain

df.collect_schema()                                    # dataframe.py:387
  → self.schema                                        # dataframe.py:235
    → DeferredTimeZone(self.native)                    # dataframe.py:241
    → native_to_narwhals_dtype(...)                    # dataframe.py:243
      → if duckdb_dtype_id == "timestamp with time zone"  # utils.py:195
          → deferred_time_zone.time_zone               # utils.py:196
            → fetch_rel_time_zone(rel)                 # utils.py:146
              → rel.query("duckdb_settings()", ...)    # utils.py:208 ← FAILS

Reproduction

Environment: macOS (darwin), Python 3.14.0, DuckDB 1.5.2, Narwhals 2.20.0

import duckdb
import narwhals as nw

# Connect with a read-only MotherDuck token
conn = duckdb.connect("md:?motherduck_token=<read-only-token>")

# Test 1: Raw duckdb_settings() via conn.sql() — PASSES
conn.sql("SELECT value FROM duckdb_settings() WHERE name = 'TimeZone'").fetchone()
# OK: TimeZone = America/Los_Angeles

# Test 2: Narwhals on a TIMESTAMPTZ column — FAILS
rel = conn.sql("SELECT now() AS ts")
df = nw.from_native(rel)
df.collect_schema()
# FAILED: Invalid Input Error: Failed to create view 'duckdb_settings()':
# Cannot execute statement of type "RELATION" on database "my_db" which is attached in read-only mode!

# Test 3: Narwhals on non-timestamp columns — PASSES (control)
rel = conn.sql("SELECT 1 AS x, 'hello' AS y")
df = nw.from_native(rel)
df.collect_schema()
# OK: schema = Schema({'x': Int32, 'y': String})

# Test 4a: rel.query() directly — FAILS (isolates the issue)
rel = conn.sql("SELECT now() AS ts")
rel.query("duckdb_settings()", "SELECT value FROM duckdb_settings() WHERE name = 'TimeZone'").fetchone()
# FAILED: same error

# Test 4b: conn.sql() directly — PASSES
conn.sql("SELECT value FROM duckdb_settings() WHERE name = 'TimeZone'").fetchone()
# OK: TimeZone = America/Los_Angeles

Tests 4a vs 4b confirm the issue is rel.query() (creates a temp view in the read-only DB) vs conn.sql() (no temp view).

Suggested Approaches

Approach A: rel.limit(1).select(current_setting(...))

Replace rel.query() with the relational API, which does not create a temp view:

from duckdb import ConstantExpression, FunctionExpression

def fetch_rel_time_zone(rel: duckdb.DuckDBPyRelation) -> str:
    result = rel.limit(1).select(
        FunctionExpression("current_setting", ConstantExpression("TimeZone"))
    ).fetchone()
    assert result is not None
    return result[0]

Pros: No API change. Fixes it for all users. Stays on the same connection as the relation (respects custom timezone). No temp view creation.
Cons: Still routes through the user's relation (mitigated by limit(1)).

Approach B: Accept optional conn parameter

Thread an optional connection through from_native() → DuckDBLazyFrame → DeferredTimeZone → fetch_rel_time_zone():

def fetch_rel_time_zone(
    rel: duckdb.DuckDBPyRelation,
    conn: duckdb.DuckDBPyConnection | None = None,
) -> str:
    if conn is not None:
        result = conn.sql(
            "SELECT value FROM duckdb_settings() WHERE name = 'TimeZone'"
        ).fetchone()
    else:
        # fallback to current behavior
        result = rel.query(
            "duckdb_settings()", "select value from duckdb_settings() where name = 'TimeZone'"
        ).fetchone()
    assert result is not None
    return result[0]

Pros: Clean separation — uses conn.sql() when available, no temp view. Per duckdb/duckdb#17033, the recommended best practice in DuckDB is to pass conn alongside DuckDBPyRelation when working with relations in functions, since there is no way to retrieve the connection from a relation.
Cons: Requires API change (from_native(rel, conn=conn)). Only helps users who know to pass conn. Risk of timezone desync if user passes a different connection than the one that created rel.

Note on DeferredTimeZone design

The existing docstring on DeferredTimeZone explains why the timezone can't be cached at from_native() time — users can change the timezone mid-session via rel.query("set timezone = '...'"). Any fix needs to preserve this lazy-fetch behavior.

[spark-dataduck]

I recognize this involves an architecture design choice around how Narwhals accesses connection-level state from a relation. I'll leave it to the maintainers to decide the right fix.

[dangotbanned]

There could definitely be a real issue here, but the current one reads like it was entirely AI generated.

Apologies if I'm missing the mark, but could you please rewrite it in your own words?

[MarcoGorelli]

thanks @spark-dataduck for the detailed report!

the current one reads like it was entirely AI generated.

I didn't get this impression at all to be honest. @spark-dataduck is employed at Motherduck and is likely writing from his own expertise on the matter

[MarcoGorelli]

Could you expand on

Cons: Still routes through the user's relation (mitigated by limit(1)).

please? What's the downside to that, and does limit(1) completely mitigate it? If so, this looks fine as a solution?

[spark-dataduck]

@dangotbanned, I use Claude Code as my companion. :) But I still do review all the text and code I write with AI. Thanks for your feedback!

@MarcoGorelli, The concern with rel.select(FunctionExpression("current_setting", ConstantExpression("TimeZone"))) was that DuckDB executes rel and applies the projection(what's passed in select()) on top. It's equivalent to:
SELECT current_setting('TimeZone') FROM <user's original query>
If <user's original query> reads from a huge table, DuckDB could scan the entire table just to evaluate current_setting('TimeZone') even though the result has nothing to do with the table data. By adding limit(1) in there, DuckDB short-circuits the table scan after producing one row, so the overhead becomes negligible. That's what I meant by "mitigated". fetchone() doesn't change the underlying SQL. It's the Python-side consumer that triggers execution and returns only one result.

If you agree, I will raise a PR.

[MarcoGorelli]

If <user's original query> reads from a huge table, DuckDB could scan the entire table just to evaluate current_setting('TimeZone') even though the result has nothing to do with the table data

What if rel was something like

rel = duckdb.sql("""from df select avg(price)""")

?

then the result only contains a single row, and limit 1 wouldn't take effect, wouldn't it still need to scan the entire table?

[spark-dataduck]

Good point! limit(1) only helps the common case (simple table scans). It doesn't help when rel involves aggregation. The underlying table scan still needs to complete to produce a single result row.

That said, this is the same behavior as the current rel.query() approach. Both rel.query() and rel.select() route through the same rel. The proposed fix doesn't make the aggregation case worse; it just doesn't make it better either. The key improvement is that rel.select() works with read-only connections, which rel.query() does not.

We can remove limit(1) if we think it doesn't help much here.

[MarcoGorelli]

thanks

The proposed fix doesn't make the aggregation case worse; it just doesn't make it better either

Any chance you could please get someone from DuckDB to confirm that this is the case please? If so, then we can change it

[spark-dataduck]

Hey, Marco! Good thing about OSS is that we are able to look at the source code and validate ourselves.

Useful duckdb-python code links are:

• rel.query() function registration
• its implementation creating a view
• rel.select() function registration: it's an alias for Project
• its implementation: calls rel->Project()

When I looked deeper,  rel.query() creates QueryRelation, while rel.select() creates ProjectionRelation in DuckDB core. Both route through the same underlying relation at execution time. The only difference is how they reference it. ProjectionRelation binds it directly as a parent-child node, while QueryRelation holds a pre-parsed SelectStatement with no reference to a parent relation. No difference in underlying table scan.