[Bug] array_agg_foreach funtion is broken

[IsmailTosunTnyl]

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

[IsmailTosunTnyl]

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)

[ixzc]

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.

[IsmailTosunTnyl]

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.

[ixzc]

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