[Bug] array_agg_foreach funtion is broken

Search before asking

I had searched in the issues and found no similar issues.

Version

tested on 2.1.6, 2.1.7, 3.0.6, master

What's Wrong?

array_agg_foreach(a) function produce different result on every call, all of them wrong

What You Expected?

function should work like in documentation
https://doris.apache.org/docs/sql-manual/sql-functions/combinators/foreach?_highlight=foreach

How to Reproduce?

-- create table 
CREATE TABLE IF NOT EXISTS db (
    id INT NOT NULL COMMENT "unique id", -- Benzersiz bir ID sütunu eklemek iyi bir pratik
    a ARRAY<INT> NULL COMMENT "Integer array column",
    s ARRAY<STRING> NULL COMMENT "String array column"
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 10
PROPERTIES (
    "replication_allocation" = "tag.location.default:1"
);

-- insert data 
INSERT INTO db (id, a, s) VALUES
(1, [1, 2, 3], ["ab", "123"]),
(2, [20], ["cd"]),
(3, [100], ["efg"]),
(4, NULL, NULL),
(5, [null, 2], [null, "c"]);

-- call function a few times, every run will return a different combination 
SELECT array_agg_foreach(a) FROM db;

Anything Else?

No response

Are you willing to submit PR?

Yes I am willing to submit a PR!

Code of Conduct

I agree to follow this project's Code of Conduct

its working fine with CTE:

mysql> WITH simple_test(a) AS ( VALUES (null), ([null,2]), ([20]),([100]),([1,2,3])) SELECT array_agg_foreach(a) FROM simple_test;
+-----------------------------------+
| array_agg_foreach(a)              |
+-----------------------------------+
| [[null, 20, 100, 1], [2, 2], [3]] |
+-----------------------------------+
1 row in set (0.01 sec)

When used with a table, it creates a different single element array for each query execution.

mysql> select * from db;
+------+-----------+---------------+
| id   | a         | s             |
+------+-----------+---------------+
|    1 | [1, 2, 3] | ["ab", "123"] |
|    3 | [100]     | ["efg"]       |
|    2 | [20]      | ["cd"]        |
|    4 | NULL      | NULL          |
|    5 | [null, 2] | [null, "c"]   |
+------+-----------+---------------+
5 rows in set (0.01 sec)

mysql> select array_agg_foreach(a) from db;
+----------------------+
| array_agg_foreach(a) |
+----------------------+
| [[20], [2], [3]]     |
+----------------------+
1 row in set (0.01 sec)

mysql> select array_agg_foreach(a) from db;
+----------------------+
| array_agg_foreach(a) |
+----------------------+
| [[null], [2], [3]]   |
+----------------------+
1 row in set (0.01 sec)

mysql> select array_agg_foreach(a) from db;
+----------------------+
| array_agg_foreach(a) |
+----------------------+
| [[null], [2], [3]]   |
+----------------------+
1 row in set (0.01 sec)

mysql> select array_agg_foreach(a) from db;
+----------------------+
| array_agg_foreach(a) |
+----------------------+
| [[100], [2], [3]]    |
+----------------------+
1 row in set (0.01 sec)

Aggregate functions (such as array_agg) may return results in an undefined order if no sorting rule is specified.
This happens because:
Doris executes aggregations using a parallel execution plan (pipeline + shuffle).
When results from multiple threads are merged, the order depends on runtime scheduling and partition merging.
As a result, the same input may produce aggregated arrays in different orders.
so it’s expected.

Aggregate functions (such as array_agg) may return results in an undefined order if no sorting rule is specified. This happens because: Doris executes aggregations using a parallel execution plan (pipeline + shuffle). When results from multiple threads are merged, the order depends on runtime scheduling and partition merging. As a result, the same input may produce aggregated arrays in different orders. so it’s expected.

The issue is not just the order of the results, but that the expected results are missing and different from the documentation. I think there might be a race condition. The CTE example returns the correct result.

Aggregate functions (such as array_agg) may return results in an undefined order if no sorting rule is specified. This happens because: Doris executes aggregations using a parallel execution plan (pipeline + shuffle). When results from multiple threads are merged, the order depends on runtime scheduling and partition merging. As a result, the same input may produce aggregated arrays in different orders. so it’s expected.

The issue is not just the order of the results, but that the expected results are missing and different from the documentation. I think there might be a race condition. The CTE example returns the correct result.

yes, thanks for your advice. we have fixed this.
#55075

Navigation Menu

Repository navigation

[Bug] array_agg_foreach funtion is broken #52252

Search before asking

Version

What's Wrong?

What You Expected?

How to Reproduce?

Anything Else?

Are you willing to submit PR?

Code of Conduct

its working fine with CTE:

When used with a table, it creates a different single element array for each query execution.

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

[Bug] array_agg_foreach funtion is broken #52252

Description

Search before asking

Version

What's Wrong?

What You Expected?

How to Reproduce?

Anything Else?

Are you willing to submit PR?

Code of Conduct

Activity

IsmailTosunTnyl commented on Jun 29, 2025

its working fine with CTE:

When used with a table, it creates a different single element array for each query execution.

ixzc commented on Aug 18, 2025

IsmailTosunTnyl commented on Aug 18, 2025

ixzc commented on Aug 20, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions