array_agg(values ORDER BY col1 DESC, col2) ordering only respects the last col2

[charleseiq]

Issue

array_agg does not order properly when given more than one column

Reproduce

SELECT
name,
date,
ARRAY_AGG(code ORDER BY rank DESC, code LIMIT 3) AS ordered_codes,
FROM (SELECT 's09ge8s4' AS uuid, 'asdf' AS name, TIMESTAMP '2021-01-01 00:00:00+00:00' AS date, ['APPLES', 'ORANGES', 'TOMATOES', 'CARROTS'] AS codes) CROSS JOIN UNNEST(codes) AS code
LEFT JOIN (SELECT 'APPLES' AS codes, 1 AS rank
UNION ALL
SELECT 'ORANGES' AS codes, 2 AS rank
UNION ALL
SELECT 'TOMATOES' AS codes, 3 AS rank
UNION ALL
SELECT 'CARROTS' AS codes, 3 AS rank) AS o ON o.codes = code
GROUP BY date, name

Outcome:

E   DataFrame.iloc[:, 2] (column name="ordered_codes") values are different (100.0 %)
E   [index]: [0]
E   [left]:  [[APPLES, CARROTS, ORANGES]]
E   [right]: [[CARROTS, TOMATOES, ORANGES]]
E   At positional index 0, first diff: ['APPLES' 'CARROTS' 'ORANGES'] != ['CARROTS', 'TOMATOES', 'ORANGES']

the values are sorted alphabetically, unlike actual BQ which respects it.

BigQuery Outcome

[ohaibbq]

@totem3 This will be closed by goccy/go-zetasqlite#144 / goccy/go-zetasqlite#174