bug: Ibis removes mandatory "RECURSIVE" keyword from BigQuery recursive CTE definition

[esuter-ki]

What happened?

I've written a moderately complex recursive BigQuery SQL query which includes a recursive CTE, which works fine if I use Google's SQL Studio web client or another client like DBeaver, however it will not work if I use Ibis' BQ backend .sql() to create a new table object.
The error when calling .execute() is as follows:

google.api_core.exceptions.BadRequest: 400 Table "CTE_1" must be qualified with a dataset (e.g. dataset.table).; reason: invalid, location: CTE_1, message: Table "CTE_1" must be qualified with a dataset (e.g. dataset.table).

I'll include the full error log at the end of this bug report.
This issue can be trivially replicated by using the recursive CTE example from the BQ documentation.
Ibis (or SQL Glot?) does not include the "RECURSIVE" keyword when compiling the table expression, and this appears to be the cause of the issue, where the following query:

WITH RECURSIVE
  CTE_1 AS (
    (SELECT 1 AS iteration UNION ALL SELECT 1 AS iteration)
    UNION ALL
    SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 3
  )
SELECT iteration FROM CTE_1
ORDER BY 1 ASC

becomes:

WITH CTE_1 AS ((SELECT 1 AS iteration UNION ALL SELECT 1 AS iteration) UNION ALL SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 3) SELECT iteration FROM CTE_1 ORDER BY 1 ASC

The attached screenshots show the original query and its compiled equivalent, and the output of both queries executed in DBeaver, where the same error thrown by Ibis is visible:

---

---

---

What version of ibis are you using?

12.0.0

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

BigQuery

Relevant log output

connection.sql('''WITH RECURSIVE
  CTE_1 AS (
    (SELECT 1 AS iteration UNION ALL SELECT 1 AS iteration)
    UNION ALL
    SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 3
  )
SELECT iteration FROM CTE_1
ORDER BY 1 ASC
''').execute()

Traceback (most recent call last):
  File "C:\Program Files\JetBrains\PyCharm 2025.1.1\plugins\python-ce\helpers\pydev\_pydevd_bundle\pydevd_exec2.py", line 3, in Exec
    exec(exp, global_vars, local_vars)
    ~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<input>", line 9, in <module>
  File "C:\Users\username\AppData\Local\pypoetry\Cache\virtualenvs\bq-project-4vtyIWVo-py3.13\Lib\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 "C:\Users\username\AppData\Local\pypoetry\Cache\virtualenvs\bq-project-4vtyIWVo-py3.13\Lib\site-packages\ibis\backends\bigquery\__init__.py", line 1061, in execute
    query = self._to_query(
        table_expr,
    ...<3 lines>...
        **kwargs,
    )
  File "C:\Users\username\AppData\Local\pypoetry\Cache\virtualenvs\bq-project-4vtyIWVo-py3.13\Lib\site-packages\ibis\backends\bigquery\__init__.py", line 958, in _to_query
    return self.raw_sql(
           ~~~~~~~~~~~~^
        sql,
        ^^^^
        params=params,
        ^^^^^^^^^^^^^^
        query_job_config=query_job_config,
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "C:\Users\username\AppData\Local\pypoetry\Cache\virtualenvs\bq-project-4vtyIWVo-py3.13\Lib\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 "C:\Users\username\AppData\Local\pypoetry\Cache\virtualenvs\bq-project-4vtyIWVo-py3.13\Lib\site-packages\ibis\backends\bigquery\__init__.py", line 195, in _client_query
    return self.client.query_and_wait(query, **kwargs)
           ~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^
  File "C:\Users\username\AppData\Local\pypoetry\Cache\virtualenvs\bq-project-4vtyIWVo-py3.13\Lib\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 "C:\Users\username\AppData\Local\pypoetry\Cache\virtualenvs\bq-project-4vtyIWVo-py3.13\Lib\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 "C:\Users\username\AppData\Local\pypoetry\Cache\virtualenvs\bq-project-4vtyIWVo-py3.13\Lib\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 "C:\Users\username\AppData\Local\pypoetry\Cache\virtualenvs\bq-project-4vtyIWVo-py3.13\Lib\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 "C:\Users\username\AppData\Local\pypoetry\Cache\virtualenvs\bq-project-4vtyIWVo-py3.13\Lib\site-packages\google\cloud\bigquery\job\query.py", line 1773, in result
    while not is_job_done():
              ~~~~~~~~~~~^^
  File "C:\Users\username\AppData\Local\pypoetry\Cache\virtualenvs\bq-project-4vtyIWVo-py3.13\Lib\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 "C:\Users\username\AppData\Local\pypoetry\Cache\virtualenvs\bq-project-4vtyIWVo-py3.13\Lib\site-packages\google\api_core\retry\retry_unary.py", line 156, in retry_target
    next_sleep = _retry_error_helper(
        exc,
    ...<6 lines>...
        timeout,
    )
  File "C:\Users\username\AppData\Local\pypoetry\Cache\virtualenvs\bq-project-4vtyIWVo-py3.13\Lib\site-packages\google\api_core\retry\retry_base.py", line 214, in _retry_error_helper
    raise final_exc from source_exc
  File "C:\Users\username\AppData\Local\pypoetry\Cache\virtualenvs\bq-project-4vtyIWVo-py3.13\Lib\site-packages\google\api_core\retry\retry_unary.py", line 147, in retry_target
    result = target()
  File "C:\Users\username\AppData\Local\pypoetry\Cache\virtualenvs\bq-project-4vtyIWVo-py3.13\Lib\site-packages\google\cloud\bigquery\job\query.py", line 1722, in is_job_done
    raise job_failed_exception
google.api_core.exceptions.BadRequest: 400 Table "CTE_1" must be qualified with a dataset (e.g. dataset.table).; reason: invalid, location: CTE_1, message: Table "CTE_1" must be qualified with a dataset (e.g. dataset.table).

Code of Conduct

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