bug(.sql): errors if there's a CTE in the .sql call

[turntable-justin]

What happened?

.sql() doesn't work when there are ctes in the sql code.

Repro code:

import ibis

backends = ["duckdb", "polars", "sqlite"]

for backend in backends:
    con = ibis.backends.connect(f"{backend}://")
    t = ibis.examples.penguins.fetch(backend=con)

    try:
        t = ibis.examples.penguins.fetch(backend=con)
        sql = "with x as (select * from penguins) select * from x"
        t.sql(sql)
        print(t.execute())
    except Exception as e:
        print(f"failed for {backend} because of error {e}")

This fails on all backends. Interestingly, sql = `select * from (with x as (select * from penguins) select * from x)" succeeds in most backends.

The function causing this seems to be _get_sql_string_view_schema in ibis/backends/sql/init.py.

What version of ibis are you using?

9.0

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

Confirmed on Bigquery, DuckDB, polars, and sqlite

Relevant log output

Traceback (most recent call last):
  File "/Users/justin/Documents/GitHub/vinyl/test2.py", line 10, in <module>
    t.sql("with x as (select * from penguins) select * from x")
  File "/Users/justin/Library/Caches/pypoetry/virtualenvs/vinyl-cwr2Pa_2-py3.11/lib/python3.11/site-packages/ibis/expr/types/relations.py", line 3322, in sql
    schema = backend._get_sql_string_view_schema(name, expr, query)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/justin/Library/Caches/pypoetry/virtualenvs/vinyl-cwr2Pa_2-py3.11/lib/python3.11/site-packages/ibis/backends/sql/__init__.py", line 230, in _get_sql_string_view_schema
    return self._get_schema_using_query(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/justin/Library/Caches/pypoetry/virtualenvs/vinyl-cwr2Pa_2-py3.11/lib/python3.11/site-packages/ibis/backends/duckdb/__init__.py", line 1507, in _get_schema_using_query
    with self._safe_raw_sql(f"DESCRIBE {query}") as cur:
  File "/Users/justin/.pyenv/versions/3.11.8/lib/python3.11/contextlib.py", line 137, in __enter__
    return next(self.gen)
           ^^^^^^^^^^^^^^
  File "/Users/justin/Library/Caches/pypoetry/virtualenvs/vinyl-cwr2Pa_2-py3.11/lib/python3.11/site-packages/ibis/backends/duckdb/__init__.py", line 358, in _safe_raw_sql
    yield self.raw_sql(*args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/justin/Library/Caches/pypoetry/virtualenvs/vinyl-cwr2Pa_2-py3.11/lib/python3.11/site-packages/ibis/backends/duckdb/__init__.py", line 104, in raw_sql
    return self.con.execute(query, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
duckdb.duckdb.CatalogException: Catalog Error: Table with name x does not exist!
Did you mean "pg_am"?
LINE 1: ...ELECT * FROM "penguins") SELECT * FROM x

Code of Conduct

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