Generated SQL for Array Aggregations with Order By doesn't work in BigQuery

[yjabri]

Hi there, I ran into an issue using Ibis and I was able to trace the problem back sqlglot.

A minimal (ish) reproduction is

>>> from sqlglot import select, expressions as exp
>>>
>>> query = (
...     select(
...         exp.Column(this="color"),
...         exp.ArrayAgg(
...             this=exp.Order(
...                 this=exp.Column(this="id"),
...                 expressions=[
...                     exp.Ordered(
...                         this=exp.Column(this="id"),
...                     )
...                 ]
...             ),
...         ).as_("ids"),
...     )
...     .from_("colors")
...     .group_by(exp.Literal.number(1))
... )
>>>
>>> print(query.sql(dialect="bigquery"))
SELECT color, ARRAY_AGG(id ORDER BY id NULLS LAST) AS ids FROM colors GROUP BY 1

which I ran against main. I also made a table in BigQuery

CREATE TABLE `my-project.my_dataset.colors` AS (
  SELECT 1 AS id, 'red' AS color
  UNION ALL
  SELECT 2 AS id, 'red' AS color
  UNION ALL
  SELECT 3 AS id, 'blue' AS color
)

So I could actually run the query. I get

BadRequest: 400 POST https://bigquery.googleapis.com/bigquery/v2/projects/my-project/queries?prettyPrint=false: NULLS LAST not supported with ascending sort order in aggregate functions.

I fudged with the table names (project and dataset) but that's it. I tried to find some documentation but I couldn't.

edit - Relevant documentation for ARRAY_AGG can be found here