bug: BigQuery Duplicate CTE alias error when using connection.sql()

[Light2Dark]

What happened?

When executing a raw SQL query containing a CTE via connection.sql() on the BigQuery backend, then calling .to_pyarrow(), .to_pandas(), or .to_polars(), BigQuery returns a "Duplicate alias" error.

import ibis

con = ibis.bigquery.connect(
    project_id="quality-of-life-364309",
    dataset_id="dev",
)

# This is fine
result = con.sql("""
WITH tt AS (
   SELECT * FROM `dev.city_places` LIMIT 100
)
SELECT * FROM tt;
""")

# This errors
result.to_pandas()  # also errors: to_polars, to_pyarrow

This query also fails

con.sql("""
FROM `dev.city_places`
|> AGGREGATE COUNT(*) AS records;
""").to_pandas()

BadRequest
400 Duplicate alias __tmp1 for WITH subquery at [5:4]; reason: invalidQuery, location: query, message: Duplicate alias __tmp1 for WITH subquery at [5:4] Location: asia-southeast1 Job ID: 3d80f2f3-2b54-4b09-8fc3-d99a237c4db3

Is ibis wrapping the query in a CTE?

What version of ibis are you using?

ibis-framework[11.0.0]

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

BigQuery

Relevant log output

Traceback (most recent call last):
  File "/var/folders/p0/_wdj4klx4j13wpg697gtjdx40000gp/T/marimo_85436/__marimo__cell_RRqy_.py", line 6, in <module>
    """).to_pandas()
         ~~~~~~~~~^^
  File "/Users/slourdusamy/.cache/uv/builds-v0/.tmp2S2b3b/lib/python3.13/site-packages/ibis/expr/types/relations.py", line 4062, in to_pandas
    return self.execute(params=params, limit=limit, **kwargs)
           ~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/slourdusamy/.cache/uv/builds-v0/.tmp2S2b3b/lib/python3.13/site-packages/ibis/expr/types/core.py", line 424, in execute
    return self._find_backend(use_default=True).execute(
           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        self, limit=limit, params=params, **kwargs
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/Users/slourdusamy/.cache/uv/builds-v0/.tmp2S2b3b/lib/python3.13/site-packages/ibis/backends/bigquery/__init__.py", line 1003, in execute
    query = self._to_query(
        table_expr,
    ...<3 lines>...
        **kwargs,
    )
  File "/Users/slourdusamy/.cache/uv/builds-v0/.tmp2S2b3b/lib/python3.13/site-packages/ibis/backends/bigquery/__init__.py", line 900, in _to_query
    return self.raw_sql(
           ~~~~~~~~~~~~^
        sql,
        ^^^^
        params=params,
        ^^^^^^^^^^^^^^
        query_job_config=query_job_config,
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/Users/slourdusamy/.cache/uv/builds-v0/.tmp2S2b3b/lib/python3.13/site-packages/ibis/backends/bigquery/__init__.py", line 801, in raw_sql
    return self._client_query(
           ~~~~~~~~~~~~~~~~~~^
        query, job_config=job_config, project=self.billing_project
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/Users/slourdusamy/.cache/uv/builds-v0/.tmp2S2b3b/lib/python3.13/site-packages/ibis/backends/bigquery/__init__.py", line 195, in _client_query
    return self.client.query_and_wait(query, **kwargs)
           ~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^
  File "/Users/slourdusamy/.cache/uv/builds-v0/.tmp2S2b3b/lib/python3.13/site-packages/google/cloud/bigquery/client.py", line 3679, in query_and_wait
    return self._query_and_wait_bigframes(
           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        query,
        ^^^^^^
    ...<8 lines>...
        max_results=max_results,
        ^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/Users/slourdusamy/.cache/uv/builds-v0/.tmp2S2b3b/lib/python3.13/site-packages/google/cloud/bigquery/client.py", line 3725, in _query_and_wait_bigframes
    return _job_helpers.query_and_wait(
           ~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        self,
        ^^^^^
    ...<10 lines>...
        callback=callback,
        ^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/Users/slourdusamy/.cache/uv/builds-v0/.tmp2S2b3b/lib/python3.13/site-packages/google/cloud/bigquery/_job_helpers.py", line 506, in query_and_wait
    return _wait_or_cancel(
        query_jobs_insert(
    ...<17 lines>...
        callback=callback,
    )
  File "/Users/slourdusamy/.cache/uv/builds-v0/.tmp2S2b3b/lib/python3.13/site-packages/google/cloud/bigquery/_job_helpers.py", line 712, in _wait_or_cancel
    query_results = job.result(
        page_size=page_size,
    ...<2 lines>...
        timeout=wait_timeout,
    )
  File "/Users/slourdusamy/.cache/uv/builds-v0/.tmp2S2b3b/lib/python3.13/site-packages/google/cloud/bigquery/job/query.py", line 1773, in result
    while not is_job_done():
              ~~~~~~~~~~~^^
  File "/Users/slourdusamy/.cache/uv/builds-v0/.tmp2S2b3b/lib/python3.13/site-packages/google/api_core/retry/retry_unary.py", line 294, in retry_wrapped_func
    return retry_target(
        target,
    ...<3 lines>...
        on_error=on_error,
    )
  File "/Users/slourdusamy/.cache/uv/builds-v0/.tmp2S2b3b/lib/python3.13/site-packages/google/api_core/retry/retry_unary.py", line 156, in retry_target
    next_sleep = _retry_error_helper(
        exc,
    ...<6 lines>...
        timeout,
    )
  File "/Users/slourdusamy/.cache/uv/builds-v0/.tmp2S2b3b/lib/python3.13/site-packages/google/api_core/retry/retry_base.py", line 214, in _retry_error_helper
    raise final_exc from source_exc
  File "/Users/slourdusamy/.cache/uv/builds-v0/.tmp2S2b3b/lib/python3.13/site-packages/google/api_core/retry/retry_unary.py", line 147, in retry_target
    result = target()
  File "/Users/slourdusamy/.cache/uv/builds-v0/.tmp2S2b3b/lib/python3.13/site-packages/google/cloud/bigquery/job/query.py", line 1722, in is_job_done
    raise job_failed_exception
google.api_core.exceptions.BadRequest: 400 Duplicate alias tts for WITH subquery at [6:4]; reason: invalidQuery, location: query, message: Duplicate alias tts for WITH subquery at [6:4]

Location: asia-southeast1
Job ID: b3a5db53-bfe6-4d92-8182-32e888f29517

Code of Conduct

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

[JonAnCla]

thanks can you also confirm which version of sqlglot you are using as there are known issues with 28.x at the moment?
see e.g. #11821

[Light2Dark]

v28.5.0
Oh, you are right! Cannot reproduce this with sqlglot v27.39.0

[JonAnCla]

thanks I'll leave this open for now, it probably is due ultimately to #11767 but would need further investigation to confirm

[asiunov]

@JonAnCla I've debugged this issue.
This happens after this change in sqlglot: tobymao/sqlglot#6330 , which renamed some args like from -> from_ , with -> with_, etc.
As a result such code doesn't work properly anymore

ibis/ibis/backends/sql/compilers/base.py

Line 688 in 511d686

out.args.pop("with", None)

I'm not familiar well with ibis to catch all such cases, but I found at least 3 such mentions of "with" argument in ibis codebase.

[asiunov]

a simple repro:

>>> import ibis
>>> print(ibis.duckdb.connect().sql("WITH t AS (SELECT 1 AS c) SELECT * FROM t").to_sql())
WITH t AS (
  SELECT
    1 AS c
), t AS (
  SELECT
    1 AS c
)
SELECT
  *
FROM t

[JonAnCla]

Thanks agree it's definitely related to #11767 in that case