Repository navigation
DuckDB: a loss that reads a view with a multiply-read CTE fails (Not implemented Error: CTE) #195
Description
Summary
On DuckDB, a loss that reads a view fails if the view has a CTE that it reads more than once:
duckdb.NotImplementedException: Not implemented Error: CTE
The error comes from DuckDB's Substrait producer (get_substrait) in DuckDBBackend._plan. When a CTE is read more than once, DuckDB materializes it, and the producer has no Substrait for that operator. Written directly in the query, the same CTE plans fine, because ddx splits a query's shared CTEs itself. Inside a view, it reaches the producer as written.
This blocks all of xarray-sql's ML demos on DuckDB. Each model is a view, and a model reads its activations more than once: softmax weights feed both the normalizer and the output, and a layer norm reads its input three times.
Repro (ddxdb 0.4.1, DuckDB 1.5.6)
import duckdb
from ddxdb import duckdb as ad
con = duckdb.connect()
con.execute("CREATE TABLE w AS SELECT i, val::DOUBLE AS val FROM (VALUES (0, 1.0), (1, 2.0)) v(i, val)")
con.execute("""CREATE VIEW m AS
WITH s AS (SELECT i, val * 2 AS y FROM w)
SELECT a.i, a.y * b.y AS y FROM s a JOIN s b ON a.i = b.i""")
ad.sql(con, "WITH loss AS (SELECT SUM(y) AS l FROM m) SELECT i, grad(loss, val) AS g FROM w")
# NotImplementedException: Not implemented Error: CTE| Variant | Result |
|---|---|
| View whose CTE is read once | works |
| View whose CTE is read twice | fails as above |
The same view with WITH s AS NOT MATERIALIZED (...) |
works: [(0, 8.0), (1, 16.0)] |
get_substrait('SELECT SUM(y) FROM m') on its own, no ddx |
fails the same way |
get_substrait on the CTE written directly in the query |
works |
Possible fixes
- Expand views before planning. On the planner connection, replace each view a loss reads with its SQL as a subquery or CTE, so ddx's own CTE splitting sees the view's shared CTEs.
- Mirror views with their CTEs marked
NOT MATERIALIZEDon the planner connection. It's only a schema copy, so how it executes doesn't matter, and the producer then gets inlined CTEs. Rewriting the view text needs a parse, so this is probably simpler to do on the parsed AST. - At minimum, refuse the case with a typed ddx error that names the view and the CTE, rather than DuckDB's bare "Not implemented Error: CTE".
A workaround on the caller's side isn't great. NOT MATERIALIZED is DuckDB- and Postgres-only syntax (DataFusion's parser refuses it), so a model written once for both engines can't use it. It would also make DuckDB's own forward queries recompute the shared CTE.
Found while running xarray-sql's ML demos on ddxdb 0.4.1.
Activity
fix(ddx-ad, ddxdb): differentiate xarray-sql's models on DuckDB
alxmrs commentedon Oct 25, 2026
Two cases still raise
Not implemented Error: CTEat 3c95aa7 (built locally withmaturin develop --release, duckdb 1.5.6). A view whose CTE is read twice now works when everygradtable is on the path (case A below). The failures are agradwith respect to a table the view doesn't read (B), and a CTE with a window read twice in a view (D). D written without the view (all CTEs in the query) works and gives the same gradient as the window read once (C).B is expected to give zeros. With the same view, B also fails as a second
grad(loss, z.v)column alongsidegrad(loss, x.v); that's how it shows up in xarray-sql's attention demo, where the loss takes gradients with respect to q, k and v. D is the softmax in the window spelling of attention (exp(s - MAX(s) OVER (PARTITION BY i))read by both the normalizer and the output), so the window spelling and the GPT demo still fail on DuckDB. The GROUP BY spelling of attention now differentiates on DuckDB.Take gradients on DuckDB through ddx
Take gradients on DuckDB through ddx
Take gradients on DuckDB through ddx
Take gradients on DuckDB through ddx