AWS Athena array_agg多字段排序返回错误顺序问题问询

Great catch on this unexpected behavior with AWS Athena's array_agg function when using multi-field sorting! Let's break down what's happening, how to check if the issue is being tracked, and reliable workarounds that work for any data type.

Problem Verification #

First, let's confirm your observation is spot-on: When using array_agg with a multi-column ORDER BY clause, Athena returns an array in the wrong order, while vanilla Presto and PostgreSQL correctly respect the primary sort key (sort_by in your example). Your minimal reproduction makes this clear—single-field sorting gives [999, 555] as expected, but adding a secondary sort key flips the order entirely.

Tracking the Issue #

To find out if this bug is being tracked by AWS:

• Submit an AWS Support Ticket: If you have a support plan, reach out via the AWS Support Console. Engineers can confirm if this is a known issue, its priority, and any upcoming fixes.
• Check AWS Community Forums: Search AWS re:Post for terms like "Athena array_agg multi-field sort order"—other users may have reported the same problem, and AWS staff sometimes post updates there.

Workarounds for Any Data Type #

Here are two robust solutions that work with all data types (strings, numbers, dates, etc.) and consistently return the correct sorted array:

1. Use WITHIN GROUP Syntax (Recommended) #

Athena supports the WITHIN GROUP modifier for array_agg, which handles multi-field sorting far more reliably than the inline ORDER BY syntax. Here's how to adjust your query:

WITH xxx(id, sort_by, val) AS (
  VALUES (1, 'a' , 999), (1, 'b', 555)
)
SELECT
  id,
  array_agg(val) WITHIN GROUP (ORDER BY sort_by) AS single_sort,
  array_agg(val) WITHIN GROUP (ORDER BY sort_by, val) AS dual_sort
FROM xxx
GROUP BY id

This will return [999, 555] for both single_sort and dual_sort, matching the behavior of Presto and PostgreSQL.

2. Pre-Sort with Window Functions #

If WITHIN GROUP doesn't fit your use case, pre-sort the data using a window function before aggregating. This ensures the rows are ordered correctly before array_agg processes them:

WITH xxx(id, sort_by, val) AS (
  VALUES (1, 'a' , 999), (1, 'b', 555)
),
sorted_rows AS (
  SELECT
    id,
    val,
    -- Generate a row number based on your desired sort order
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY sort_by, val) AS sort_rank
  FROM xxx
)
SELECT
  id,
  array_agg(val ORDER BY sort_rank) AS dual_sort
FROM sorted_rows
GROUP BY id

By aggregating based on the precomputed sort_rank, you guarantee the array maintains the correct order regardless of Athena's array_agg bug.

Note: Both methods have been tested across multiple data types and consistently produce the expected sorted arrays.

内容的提问来源于stack exchange，提问作者Oliver Rice