bug: Duplicate CTE Name in recursive .sql() query with duckdb backend

[john-lanious]

What happened?

The sql query is taken from DuckDB's documentation here https://duckdb.org/2025/05/23/using-key#iterate-like-its-1999-recursive-ctes

The below snippet fails when run via .sql(), but succeeds via .raw_sql().fetchall().

ibis.options.interactive = True
conn = ibis.connect("duckdb://")

conn.sql("""
WITH RECURSIVE power(a, b, c) AS (
    SELECT 2, 0, 1       -- 2^0 = 1
        UNION
    SELECT a, b+1, a * c -- a^(b+1) = a * a^b
    FROM power           -- reads the working table (contains a single row)
    WHERE a * c < 100
)
FROM power;
""")

What version of ibis are you using?

ibis-framework==11.0.0
duckdb==1.4.3

What backend(s) are you using, if any?

DuckDB

Relevant log output

---------------------------------------------------------------------------
ParserException                           Traceback (most recent call last)
File ~/projects/.venv/lib/python3.14/site-packages/IPython/core/formatters.py:770, in PlainTextFormatter.__call__(self, obj)
    763 stream = StringIO()
    764 printer = pretty.RepresentationPrinter(stream, self.verbose,
    765     self.max_width, self.newline,
    766     max_seq_length=self.max_seq_length,
    767     singleton_pprinters=self.singleton_printers,
    768     type_pprinters=self.type_printers,
    769     deferred_pprinters=self.deferred_printers)
--> 770 printer.pretty(obj)
    771 printer.flush()
    772 return stream.getvalue()

File ~/projects/.venv/lib/python3.14/site-packages/IPython/lib/pretty.py:412, in RepresentationPrinter.pretty(self, obj)
    401                         return meth(obj, self, cycle)
    402                 if (
    403                     cls is not object
    404                     # check if cls defines __repr__
   (...)    410                     and callable(_safe_getattr(cls, "__repr__", None))
    411                 ):
--> 412                     return _repr_pprint(obj, self, cycle)
    414     return _default_pprint(obj, self, cycle)
    415 finally:

File ~/projects/.venv/lib/python3.14/site-packages/IPython/lib/pretty.py:787, in _repr_pprint(obj, p, cycle)
    785 """A pprint that just redirects to the normal repr function."""
    786 # Find newlines and replace them with p.break_()
--> 787 output = repr(obj)
    788 lines = output.splitlines()
    789 with p.group():

File ~/projects/.venv/lib/python3.14/site-packages/ibis/expr/types/core.py:55, in Expr.__repr__(self)
     53 def __repr__(self) -> str:
     54     if ibis.options.interactive:
---> 55         return capture_rich_renderable(self)
     56     else:
     57         return self._noninteractive_repr()

File ~/projects/.venv/lib/python3.14/site-packages/ibis/expr/types/rich.py:48, in capture_rich_renderable(renderable)
     46 console = Console(force_terminal=False)
     47 with _with_rich_display_disabled(), console.capture() as capture:
---> 48     console.print(renderable)
     49 return capture.get().rstrip()

File ~/projects/.venv/lib/python3.14/site-packages/rich/console.py:1724, in Console.print(self, sep, end, style, justify, overflow, no_wrap, emoji, markup, highlight, width, height, crop, soft_wrap, new_line_start, *objects)
   1722 if style is None:
   1723     for renderable in renderables:
-> 1724         extend(render(renderable, render_options))
   1725 else:
   1726     for renderable in renderables:

File ~/projects/.venv/lib/python3.14/site-packages/rich/console.py:1325, in Console.render(self, renderable, options)
   1323 renderable = rich_cast(renderable)
   1324 if hasattr(renderable, "__rich_console__") and not isclass(renderable):
-> 1325     render_iterable = renderable.__rich_console__(self, _options)
   1326 elif isinstance(renderable, str):
   1327     text_renderable = self.render_str(
   1328         renderable, highlight=_options.highlight, markup=_options.markup
   1329     )

File ~/projects/.venv/lib/python3.14/site-packages/ibis/expr/types/core.py:76, in Expr.__rich_console__(self, console, options)
     74 try:
     75     if opts.interactive:
---> 76         rich_object = to_rich(self, console_width=console_width)
     77     else:
     78         rich_object = Text(self._noninteractive_repr())

File ~/projects/.venv/lib/python3.14/site-packages/ibis/expr/types/rich.py:70, in to_rich(expr, max_rows, max_columns, max_length, max_string, max_depth, console_width)
     66     return to_rich_scalar(
     67         expr, max_length=max_length, max_string=max_string, max_depth=max_depth
     68     )
     69 else:
---> 70     return to_rich_table(
     71         expr,
     72         max_rows=max_rows,
     73         max_columns=max_columns,
     74         max_length=max_length,
     75         max_string=max_string,
     76         max_depth=max_depth,
     77         console_width=console_width,
     78     )

File ~/projects/.venv/lib/python3.14/site-packages/ibis/expr/types/_rich.py:331, in to_rich_table(tablish, max_rows, max_columns, max_length, max_string, max_depth, console_width)
    328     if orig_ncols > len(computed_cols):
    329         table = table.select(*computed_cols)
--> 331 result = table.limit(max_rows + 1).to_pyarrow()
    332 # Now format the columns in order, stopping if the console width would
    333 # be exceeded.
    334 col_info = []

File ~/projects/.venv/lib/python3.14/site-packages/ibis/expr/types/relations.py:625, in Table.to_pyarrow(self, params, limit, **kwargs)
    617 @experimental
    618 def to_pyarrow(
    619     self,
   (...)    623     **kwargs: Any,
    624 ) -> pa.Table:
--> 625     return super().to_pyarrow(params=params, limit=limit, **kwargs)

File ~/projects/.venv/lib/python3.14/site-packages/ibis/expr/types/core.py:605, in Expr.to_pyarrow(self, params, limit, **kwargs)
    575 @experimental
    576 def to_pyarrow(
    577     self,
   (...)    581     **kwargs: Any,
    582 ) -> pa.Table | pa.Array | pa.Scalar:
    583     """Execute expression to a pyarrow object.
    584 
    585     This method is eager and will execute the associated expression
   (...)    603         If the passed expression is a Scalar, a pyarrow scalar is returned.
    604     """
--> 605     return self._find_backend(use_default=True).to_pyarrow(
    606         self, params=params, limit=limit, **kwargs
    607     )

File ~/projects/.venv/lib/python3.14/site-packages/ibis/backends/duckdb/__init__.py:1388, in Backend.to_pyarrow(self, expr, params, limit, **kwargs)
   1377 def to_pyarrow(
   1378     self,
   1379     expr: ir.Expr,
   (...)   1384     **kwargs: Any,
   1385 ) -> pa.Table:
   1386     from ibis.backends.duckdb.converter import DuckDBPyArrowData
-> 1388     table = self._to_duckdb_relation(
   1389         expr, params=params, limit=limit, **kwargs
   1390     ).to_arrow_table()
   1391     return expr.__pyarrow_result__(table, data_mapper=DuckDBPyArrowData)

File ~/projects/.venv/lib/python3.14/site-packages/ibis/backends/duckdb/__init__.py:1332, in Backend._to_duckdb_relation(self, expr, params, limit, **kwargs)
   1330 if table_expr.schema().geospatial:
   1331     self._load_extensions(["spatial"])
-> 1332 return self.con.sql(sql)

ParserException: Parser Error: Duplicate CTE name "power"

Code of Conduct

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