bug: ibis bigquery failed for recursive CTE

[ruiyang2015]

What happened?

for BigQuery, if we use a Recursive CTE statement something like this:

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

if we try to get the the ibis SQL expression, we will get an error of

WITH RECURSIVE is only allowed at the top level of the SELECT

The problem seems caused by ibis.sql() function will wrap a 'select * from ()' instead of directly using the raw query passed in.

It will be good to not wrap this select * to avoid such issue.

What version of ibis are you using?

9.2.0

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

BigQuery

Relevant log output

No response

Code of Conduct

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