bug: BigQuery ARRAY_AGG incorrect use of clauses when used with a window

[asikeero-elisa]

What happened?

With BigQuery Ibis adds the IGNORE NULLS clause to the ARRAY_AGG function which is fine on its own, but if used with a window, it creates an error.

Here is an example SQL output:

import ibis

bq = ibis.bigquery.connect(project_id="bigquery-public-data", dataset_id="samples")
t = bq.table("gsod")
window = ibis.window(preceding=1, following=0)
t.mean_temp.collect().over(window).to_sql()

which generates:

SELECT
  ARRAY_AGG(`t0`.`mean_temp` IGNORE NULLS) OVER (ROWS BETWEEN 1 preceding AND CURRENT ROW) AS `ArrayCollect_mean_temp_`
FROM `bigquery-public-data`.`samples`.`gsod` AS `t0`

The BigQuery docs state If this function is used with the OVER clause, it's part of a window function call. In a window function call, aggregate function clauses can't be used.

What version of ibis are you using?

10.8.0

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

BigQuery

Relevant log output

BadRequest: 400 Analytic function array_agg does not support IGNORE NULLS or RESPECT NULLS.; reason: invalidQuery, location: query, message: Analytic function array_agg does not support IGNORE NULLS or RESPECT NULLS.

Code of Conduct

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

[asikeero-elisa]

After some digging I learned that a way to circumvent this issue is to use collect(include_null=True) which leaves the ARRAY_AGG without any clause. It solves my immediate issue, but I guess the bug is still valid.