You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by gi...@apache.org on 2026/07/25 19:08:42 UTC
(datafusion) branch main updated: test: cover `array_agg(DISTINCT)` on dictionaries and bounded `retract_batch` memory (#23873)
This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/datafusion.git
The following commit(s) were added to refs/heads/main by this push:
new 68a676e1c1 test: cover `array_agg(DISTINCT)` on dictionaries and bounded `retract_batch` memory (#23873)
68a676e1c1 is described below
commit 68a676e1c1e2719aa8b985616b121c1b1b288ef2
Author: Andrew Lamb <an...@nerdnetworks.org>
AuthorDate: Sat Jul 25 14:53:22 2026 -0400
test: cover `array_agg(DISTINCT)` on dictionaries and bounded `retract_batch` memory (#23873)
## Which issue does this PR close?
- related to https://github.com/apache/datafusion/pull/23716
## Rationale for this change
It adds test coverage for two gaps found while reviewing
https://github.com/apache/datafusion/pull/23716.
## What changes are included in this PR?
Tests only, no functional change.
- Dictionary inputs
- memory usage on retract (make sure memory is released)
Note I moved `array_agg` cases out `aggregate.slt` as it is already more
than 9k lines long
## Are these changes tested?
They are only tests
## Are there any user-facing changes?
No. Tests only, no public API changes.
---------
Co-authored-by: Claude Opus 5 (1M context) <no...@anthropic.com>
---
datafusion/functions-aggregate/src/array_agg.rs | 49 ++
datafusion/sqllogictest/test_files/aggregate.slt | 510 -------------------
datafusion/sqllogictest/test_files/array_agg.slt | 620 +++++++++++++++++++++++
3 files changed, 669 insertions(+), 510 deletions(-)
diff --git a/datafusion/functions-aggregate/src/array_agg.rs b/datafusion/functions-aggregate/src/array_agg.rs
index b563a6389e..4b0a9d3dda 100644
--- a/datafusion/functions-aggregate/src/array_agg.rs
+++ b/datafusion/functions-aggregate/src/array_agg.rs
@@ -2708,4 +2708,53 @@ mod tests {
assert_eq!(values, vec![100i32, 200, 300, 400]);
Ok(())
}
+
+ #[test]
+ fn distinct_retract_memory_is_bounded() -> Result<()> {
+ use arrow::array::Int64Array;
+
+ // Emulates `ROWS BETWEEN CURRENT ROW AND CURRENT ROW`: every row enters
+ // the frame and immediately leaves it again. Only `CARDINALITY` distinct
+ // values are ever seen and the live set never holds more than one of
+ // them
+ const CARDINALITY: i64 = 10;
+ const WARMUP_ROWS: i64 = 1_000;
+ const EXTRA_ROWS: i64 = 20_000;
+
+ let mut acc =
+ DistinctArrayAggAccumulator::try_new(&DataType::Int64, None, false)?;
+
+ let slide = |acc: &mut DistinctArrayAggAccumulator, rows: i64| -> Result<()> {
+ for i in 0..rows {
+ let value: ArrayRef = Arc::new(Int64Array::from(vec![i % CARDINALITY]));
+ acc.update_batch(std::slice::from_ref(&value))?;
+ acc.retract_batch(std::slice::from_ref(&value))?;
+ }
+ Ok(())
+ };
+
+ // Let every buffer reach its steady state before taking a baseline.
+ slide(&mut acc, WARMUP_ROWS)?;
+ let baseline = acc.size();
+
+ slide(&mut acc, EXTRA_ROWS)?;
+ let grown = acc.size();
+
+ assert!(
+ grown <= 2 * baseline,
+ "size() must not grow with the number of retracted rows: \
+ {baseline} bytes after {WARMUP_ROWS} rows, \
+ {grown} bytes after {} rows",
+ WARMUP_ROWS + EXTRA_ROWS
+ );
+
+ // Everything was retracted, so nothing is left in the frame.
+ let result = acc.evaluate()?;
+ assert!(
+ matches!(&result, ScalarValue::List(arr) if arr.is_null(0)),
+ "expected null list after retracting every row, got {result:?}"
+ );
+
+ Ok(())
+ }
}
diff --git a/datafusion/sqllogictest/test_files/aggregate.slt b/datafusion/sqllogictest/test_files/aggregate.slt
index 1515e17e3f..8e7a9639f5 100644
--- a/datafusion/sqllogictest/test_files/aggregate.slt
+++ b/datafusion/sqllogictest/test_files/aggregate.slt
@@ -212,228 +212,6 @@ WITHIN GROUP (ORDER BY c3)
OVER (ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
FROM aggregate_test_100
-# array agg can use order by
-query ?
-SELECT array_agg(c13 ORDER BY c13)
-FROM
- (SELECT *
- FROM aggregate_test_100
- ORDER BY c13
- LIMIT 5) as t1
-----
-[0VVIHzxWtNOFLtnhjHEKjXaJOSLJfm, 0keZ5G8BffGwgF2RwQD59TFzMStxCB, 0og6hSkhbX8AC1ktFS4kounvTzy8Vo, 1aOcrEGd0cOqZe2I5XBOm0nDcwtBZO, 2T3wSlHdEmASmO0xcXHnndkKEt6bz8]
-
-# array agg can use order by with distinct
-query ?
-SELECT array_agg(DISTINCT c13 ORDER BY c13)
-FROM
- (SELECT *
- FROM aggregate_test_100
- ORDER BY c13
- LIMIT 5) as t1
-----
-[0VVIHzxWtNOFLtnhjHEKjXaJOSLJfm, 0keZ5G8BffGwgF2RwQD59TFzMStxCB, 0og6hSkhbX8AC1ktFS4kounvTzy8Vo, 1aOcrEGd0cOqZe2I5XBOm0nDcwtBZO, 2T3wSlHdEmASmO0xcXHnndkKEt6bz8]
-
-query error Execution error: In an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
-SELECT array_agg(DISTINCT c13 ORDER BY c12)
-FROM aggregate_test_100
-
-query error Execution error: In an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
-SELECT array_agg(DISTINCT c13 ORDER BY c13, c12)
-FROM aggregate_test_100
-
-query ?? rowsort
-with tbl as (SELECT * FROM (VALUES ('xxx', 'yyy'), ('xxx', 'yyy'), ('xxx2', 'yyy2')) AS t(x, y))
-select
- array_agg(x order by x) as x_agg,
- array_agg(y order by y) as y_agg
-from tbl
-group by all
-----
-[xxx, xxx, xxx2] [yyy, yyy, yyy2]
-
-query ??
-SELECT
- (SELECT array_agg(c12 ORDER BY c12) FROM aggregate_test_100),
- (SELECT array_agg(c13 ORDER BY c13) FROM aggregate_test_100)
-----
-[0.01479305307777301, 0.02182578039211991, 0.03968347085780355, 0.04429073092078406, 0.047343434291126085, 0.04893135681998029, 0.0494924465469434, 0.05573662213439634, 0.05636955101974106, 0.061029375346466685, 0.07260475960924484, 0.09465635123783445, 0.12357539988406441, 0.152498292971736, 0.16301110515739792, 0.1640882545084913, 0.1754261586710173, 0.17592486905979987, 0.17909035118828576, 0.18628859265874176, 0.19113293583306745, 0.2145232647388039, 0.21535402343780985, 0.2489979431 [...]
-
-query ??
-SELECT
- array_agg(c12 ORDER BY c12),
- array_agg(c13 ORDER BY c13)
-FROM aggregate_test_100
-----
-[0.01479305307777301, 0.02182578039211991, 0.03968347085780355, 0.04429073092078406, 0.047343434291126085, 0.04893135681998029, 0.0494924465469434, 0.05573662213439634, 0.05636955101974106, 0.061029375346466685, 0.07260475960924484, 0.09465635123783445, 0.12357539988406441, 0.152498292971736, 0.16301110515739792, 0.1640882545084913, 0.1754261586710173, 0.17592486905979987, 0.17909035118828576, 0.18628859265874176, 0.19113293583306745, 0.2145232647388039, 0.21535402343780985, 0.2489979431 [...]
-
-query ?? rowsort
-with tbl as (SELECT * FROM (VALUES ('xxx', 'yyy'), ('xxx', 'yyy'), ('xxx2', 'yyy2')) AS t(x, y))
-select
- array_agg(distinct x order by x) as x_agg,
- array_agg(distinct y order by y) as y_agg
-from tbl
-group by all
-----
-[xxx, xxx2] [yyy, yyy2]
-
-query ??
-SELECT
- (SELECT array_agg(DISTINCT c12 ORDER BY c12) FROM aggregate_test_100),
- (SELECT array_agg(DISTINCT c13 ORDER BY c13) FROM aggregate_test_100)
-----
-[0.01479305307777301, 0.02182578039211991, 0.03968347085780355, 0.04429073092078406, 0.047343434291126085, 0.04893135681998029, 0.0494924465469434, 0.05573662213439634, 0.05636955101974106, 0.061029375346466685, 0.07260475960924484, 0.09465635123783445, 0.12357539988406441, 0.152498292971736, 0.16301110515739792, 0.1640882545084913, 0.1754261586710173, 0.17592486905979987, 0.17909035118828576, 0.18628859265874176, 0.19113293583306745, 0.2145232647388039, 0.21535402343780985, 0.2489979431 [...]
-
-query ??
-SELECT
- array_agg(DISTINCT c12 ORDER BY c12),
- array_agg(DISTINCT c13 ORDER BY c13)
-FROM aggregate_test_100
-----
-[0.01479305307777301, 0.02182578039211991, 0.03968347085780355, 0.04429073092078406, 0.047343434291126085, 0.04893135681998029, 0.0494924465469434, 0.05573662213439634, 0.05636955101974106, 0.061029375346466685, 0.07260475960924484, 0.09465635123783445, 0.12357539988406441, 0.152498292971736, 0.16301110515739792, 0.1640882545084913, 0.1754261586710173, 0.17592486905979987, 0.17909035118828576, 0.18628859265874176, 0.19113293583306745, 0.2145232647388039, 0.21535402343780985, 0.2489979431 [...]
-
-statement ok
-CREATE EXTERNAL TABLE agg_order (
-c1 INT NOT NULL,
-c2 INT NOT NULL,
-c3 INT NOT NULL
-)
-STORED AS CSV
-LOCATION '../core/tests/data/aggregate_agg_multi_order.csv'
-OPTIONS ('format.has_header' 'true');
-
-# test array_agg with order by multiple columns
-query ?
-select array_agg(c1 order by c2 desc, c3) from agg_order;
-----
-[5, 6, 7, 8, 9, 1, 2, 3, 4, 10]
-
-query TT
-explain select array_agg(c1 order by c2 desc, c3) from agg_order;
-----
-logical_plan
-01)Aggregate: groupBy=[[]], aggr=[[array_agg(agg_order.c1) ORDER BY [agg_order.c2 DESC NULLS FIRST, agg_order.c3 ASC NULLS LAST]]]
-02)--TableScan: agg_order projection=[c1, c2, c3]
-physical_plan
-01)AggregateExec: mode=Final, gby=[], aggr=[array_agg(agg_order.c1) ORDER BY [agg_order.c2 DESC NULLS FIRST, agg_order.c3 ASC NULLS LAST]]
-02)--CoalescePartitionsExec
-03)----AggregateExec: mode=Partial, gby=[], aggr=[array_agg(agg_order.c1) ORDER BY [agg_order.c2 DESC NULLS FIRST, agg_order.c3 ASC NULLS LAST]]
-04)------SortExec: expr=[c2@1 DESC, c3@2 ASC NULLS LAST], preserve_partitioning=[true]
-05)--------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
-06)----------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/core/tests/data/aggregate_agg_multi_order.csv]]}, projection=[c1, c2, c3], file_type=csv, has_header=true
-
-# Regression test: ARRAY_AGG with conflicting ASC/DESC ORDER BY in the same query.
-# get_finer_aggregate_exprs_requirement picks ASC as the common requirement and
-# reverses the DESC aggregate (is_reversed=true, ordering_req=[ASC]).
-# The optimizer then sets is_input_pre_ordered=true on both. Without the fix,
-# state() emits values reversed to DESC but ordering keys still in ASC order,
-# causing merge_batch to pair each value with the wrong key (silent wrong results).
-query TT
-explain select array_agg(c1 order by c1), array_agg(c1 order by c1 desc) from agg_order;
-----
-logical_plan
-01)Aggregate: groupBy=[[]], aggr=[[array_agg(agg_order.c1) ORDER BY [agg_order.c1 ASC NULLS LAST], array_agg(agg_order.c1) ORDER BY [agg_order.c1 DESC NULLS FIRST]]]
-02)--TableScan: agg_order projection=[c1]
-physical_plan
-01)AggregateExec: mode=Final, gby=[], aggr=[array_agg(agg_order.c1) ORDER BY [agg_order.c1 ASC NULLS LAST], array_agg(agg_order.c1) ORDER BY [agg_order.c1 DESC NULLS FIRST]]
-02)--CoalescePartitionsExec
-03)----AggregateExec: mode=Partial, gby=[], aggr=[array_agg(agg_order.c1) ORDER BY [agg_order.c1 ASC NULLS LAST], array_agg(agg_order.c1) ORDER BY [agg_order.c1 DESC NULLS FIRST]]
-04)------SortExec: expr=[c1@0 ASC NULLS LAST], preserve_partitioning=[true]
-05)--------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
-06)----------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/core/tests/data/aggregate_agg_multi_order.csv]]}, projection=[c1], file_type=csv, has_header=true
-
-query ??
-select array_agg(c1 order by c1), array_agg(c1 order by c1 desc) from agg_order;
-----
-[1, 2, 3, 4, 5, 6, 7, 8, 9, 10] [10, 9, 8, 7, 6, 5, 4, 3, 2, 1]
-
-# test array_agg_order with list data type
-statement ok
-CREATE TABLE array_agg_order_list_table AS VALUES
- ('w', 2, [1,2,3], 10),
- ('w', 1, [9,5,2], 20),
- ('w', 1, [3,2,5], 30),
- ('b', 2, [4,5,6], 20),
- ('b', 1, [7,8,9], 30)
-;
-
-query T? rowsort
-select column1, array_agg(column3 order by column2, column4 desc) from array_agg_order_list_table group by column1;
-----
-b [[7, 8, 9], [4, 5, 6]]
-w [[3, 2, 5], [9, 5, 2], [1, 2, 3]]
-
-query T?? rowsort
-select column1, first_value(column3 order by column2, column4 desc), last_value(column3 order by column2, column4 desc) from array_agg_order_list_table group by column1;
-----
-b [7, 8, 9] [4, 5, 6]
-w [3, 2, 5] [1, 2, 3]
-
-query T? rowsort
-select column1, nth_value(column3, 2 order by column2, column4 desc) from array_agg_order_list_table group by column1;
-----
-b [4, 5, 6]
-w [9, 5, 2]
-
-query ?
-select array_agg(DISTINCT column2 order by column2) from array_agg_order_list_table;
-----
-[1, 2]
-
-query ?
-select array_agg(DISTINCT column2 order by column2 desc) from array_agg_order_list_table;
-----
-[2, 1]
-
-query ?
-select array_agg(DISTINCT column2 + 1 order by column2 + 1 desc) from array_agg_order_list_table;
-----
-[3, 2]
-
-query ?
-select array_agg(DISTINCT column2 order by column2) from array_agg_order_list_table GROUP BY column1;
-----
-[1, 2]
-[1, 2]
-
-statement error In an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
-select array_agg(DISTINCT column2 order by column1) from array_agg_order_list_table;
-
-statement ok
-drop table array_agg_order_list_table;
-
-# test array_agg_distinct with list data type
-statement ok
-CREATE TABLE array_agg_distinct_list_table AS VALUES
- ('w', [0,1]),
- ('w', [0,1]),
- ('w', [1,0]),
- ('b', [1,0]),
- ('b', [1,0]),
- ('b', [1,0]),
- ('b', [0,1]),
- (NULL, [0,1]),
- ('b', NULL)
-;
-
-# Apply array_sort to have deterministic result, higher dimension nested array also works but not for array sort,
-# so they are covered in `datafusion/functions-aggregate/src/array_agg.rs`
-query ??
-select array_sort(c1), array_sort(c2) from (
- select array_agg(distinct column1) as c1, array_agg(distinct column2) ignore nulls as c2 from array_agg_distinct_list_table
-);
-----
-[NULL, b, w] [[0, 1], [1, 0]]
-
-statement ok
-drop table array_agg_distinct_list_table;
-
-# Test array_agg with DISTINCT and IGNORE NULLS (regression test for issue #19735)
-query ?
-SELECT array_sort(ARRAY_AGG(DISTINCT x IGNORE NULLS)) as result
-FROM (VALUES (1), (2), (NULL), (2), (NULL), (1)) AS t(x);
-----
-[1, 2]
# Test that non-DISTINCT aggregates also preserve IGNORE NULLS when mixed with DISTINCT
# This tests the two-phase aggregation rewrite in SingleDistinctToGroupBy
@@ -481,75 +259,6 @@ FROM (VALUES
----
2 [40, 30, 20, 10]
-statement error This feature is not implemented: Calling array_agg: LIMIT not supported in function arguments: 1
-SELECT array_agg(c13 LIMIT 1) FROM aggregate_test_100
-
-
-# Test distinct aggregate function with merge batch
-query II
-with A as (
- select 1 as id, 2 as foo
- UNION ALL
- select 1, null
- UNION ALL
- select 1, null
- UNION ALL
- select 1, 3
- UNION ALL
- select 1, 2
- ---- The order is non-deterministic, verify with length
-) select array_length(array_agg(distinct a.foo)), sum(distinct 1) from A a group by a.id;
-----
-3 1
-
-# It has only AggregateExec with FinalPartitioned mode, so `merge_batch` is used
-# If the plan is changed, whether the `merge_batch` is used should be verified to ensure the test coverage
-query TT
-explain with A as (
- select 1 as id, 2 as foo
- UNION ALL
- select 1, null
- UNION ALL
- select 1, null
- UNION ALL
- select 1, 3
- UNION ALL
- select 1, 2
-) select array_length(array_agg(distinct a.foo)), sum(distinct 1) from A a group by a.id;
-----
-logical_plan
-01)Projection: array_length(array_agg(DISTINCT a.foo)), sum(DISTINCT Int64(1))
-02)--Aggregate: groupBy=[[a.id]], aggr=[[array_agg(DISTINCT a.foo), sum(DISTINCT Int64(1))]]
-03)----SubqueryAlias: a
-04)------SubqueryAlias: a
-05)--------Union
-06)----------Projection: Int64(1) AS id, Int64(2) AS foo
-07)------------EmptyRelation: rows=1
-08)----------Projection: Int64(1) AS id, Int64(NULL) AS foo
-09)------------EmptyRelation: rows=1
-10)----------Projection: Int64(1) AS id, Int64(NULL) AS foo
-11)------------EmptyRelation: rows=1
-12)----------Projection: Int64(1) AS id, Int64(3) AS foo
-13)------------EmptyRelation: rows=1
-14)----------Projection: Int64(1) AS id, Int64(2) AS foo
-15)------------EmptyRelation: rows=1
-physical_plan
-01)ProjectionExec: expr=[array_length(array_agg(DISTINCT a.foo)@1) as array_length(array_agg(DISTINCT a.foo)), sum(DISTINCT Int64(1))@2 as sum(DISTINCT Int64(1))]
-02)--AggregateExec: mode=FinalPartitioned, gby=[id@0 as id], aggr=[array_agg(DISTINCT a.foo), sum(DISTINCT Int64(1))], ordering_mode=Sorted
-03)----RepartitionExec: partitioning=Hash([id@0], 4), input_partitions=5
-04)------AggregateExec: mode=Partial, gby=[id@0 as id], aggr=[array_agg(DISTINCT a.foo), sum(DISTINCT Int64(1))], ordering_mode=Sorted
-05)--------UnionExec
-06)----------ProjectionExec: expr=[1 as id, 2 as foo]
-07)------------PlaceholderRowExec
-08)----------ProjectionExec: expr=[1 as id, NULL as foo]
-09)------------PlaceholderRowExec
-10)----------ProjectionExec: expr=[1 as id, NULL as foo]
-11)------------PlaceholderRowExec
-12)----------ProjectionExec: expr=[1 as id, 3 as foo]
-13)------------PlaceholderRowExec
-14)----------ProjectionExec: expr=[1 as id, 2 as foo]
-15)------------PlaceholderRowExec
-
# FIX: custom absolute values
# csv_query_avg_multi_batch
@@ -2327,7 +2036,6 @@ statement ok
DROP TABLE approx_distinct_interval_test;
-
## This test executes the APPROX_PERCENTILE_CONT aggregation against the test
## data, asserting the estimated quantiles are ±5% their actual values.
##
@@ -2875,7 +2583,6 @@ d 2.444444444444 25.444444444444
e 3 40.333333333333
-
query TR
SELECT c1, approx_percentile_cont(0.95) WITHIN GROUP (ORDER BY c3 DESC) AS c3_p95 FROM aggregate_test_100 GROUP BY 1 ORDER BY 1
----
@@ -3143,23 +2850,6 @@ SELECT count(1 + 1)
----
1
-# csv_query_array_agg
-query ?
-SELECT array_agg(c13) FROM (SELECT * FROM aggregate_test_100 ORDER BY c13 LIMIT 2) test
-----
-[0VVIHzxWtNOFLtnhjHEKjXaJOSLJfm, 0keZ5G8BffGwgF2RwQD59TFzMStxCB]
-
-# csv_query_array_agg_empty
-query ?
-SELECT array_agg(c13) FROM (SELECT * FROM aggregate_test_100 LIMIT 0) test
-----
-NULL
-
-# csv_query_array_agg_one
-query ?
-SELECT array_agg(c13) FROM (SELECT * FROM aggregate_test_100 ORDER BY c13 LIMIT 1) test
-----
-[0VVIHzxWtNOFLtnhjHEKjXaJOSLJfm]
# csv_query_array_agg_with_overflow
query IIRIII
@@ -3212,12 +2902,6 @@ NULL 4 29 1.260869565217 123 -117 23
NULL 5 -194 -13.857142857143 118 -101 14
NULL NULL 781 7.81 125 -117 100
-# select with count to forces array_agg_distinct function, since single distinct expression is converted to group by by optimizer
-# csv_query_array_agg_distinct
-query ?I
-SELECT array_sort(array_agg(distinct c2)), count(1) FROM aggregate_test_100
-----
-[1, 2, 3, 4, 5] 100
# aggregate_time_min_and_max
query TT
@@ -3380,7 +3064,6 @@ SELECT max(c1) FROM test;
3
-
# count_basic
statement ok
create table t (c int) as values (1), (2), (null), (3), (null), (4), (5);
@@ -4742,177 +4425,6 @@ SELECT percentile_cont(0.75) WITHIN GROUP (ORDER BY v DESC) FROM (VALUES (1), (2
----
2.75
-# array_agg_zero
-query ?
-SELECT ARRAY_AGG([])
-----
-[[]]
-
-# array_agg_one
-query ?
-SELECT ARRAY_AGG([1])
-----
-[[1]]
-
-# test array_agg with no row qualified
-statement ok
-create table t(a int, b float, c bigint) as values (1, 1.2, 2);
-
-# returns NULL, follows DuckDB's behaviour
-query ?
-select array_agg(a) from t where a > 2;
-----
-NULL
-
-query ?
-select array_agg(b) from t where b > 3.1;
-----
-NULL
-
-query ?
-select array_agg(c) from t where c > 3;
-----
-NULL
-
-query ?I
-select array_agg(c), count(1) from t where c > 3;
-----
-NULL 0
-
-# returns 0 rows if group by is applied, follows DuckDB's behaviour
-query ?
-select array_agg(a) from t where a > 3 group by a;
-----
-
-query ?I
-select array_agg(a), count(1) from t where a > 3 group by a;
-----
-
-# returns NULL, follows DuckDB's behaviour
-query ?
-select array_agg(distinct a) from t where a > 3;
-----
-NULL
-
-query ?I
-select array_agg(distinct a), count(1) from t where a > 3;
-----
-NULL 0
-
-# returns 0 rows if group by is applied, follows DuckDB's behaviour
-query ?
-select array_agg(distinct a) from t where a > 3 group by a;
-----
-
-query ?I
-select array_agg(distinct a), count(1) from t where a > 3 group by a;
-----
-
-# test order sensitive array agg
-query ?
-select array_agg(a order by a) from t where a > 3;
-----
-NULL
-
-query ?
-select array_agg(a order by a) from t where a > 3 group by a;
-----
-
-query ?I
-select array_agg(a order by a), count(1) from t where a > 3 group by a;
-----
-
-statement ok
-drop table t;
-
-# test with no values
-statement ok
-create table t(a int, b float, c bigint);
-
-query ?
-select array_agg(a) from t;
-----
-NULL
-
-query ?
-select array_agg(b) from t;
-----
-NULL
-
-query ?
-select array_agg(c) from t;
-----
-NULL
-
-query ?I
-select array_agg(distinct a), count(1) from t;
-----
-NULL 0
-
-query ?I
-select array_agg(distinct b), count(1) from t;
-----
-NULL 0
-
-query ?I
-select array_agg(distinct b), count(1) from t;
-----
-NULL 0
-
-statement ok
-drop table t;
-
-
-# array_agg_i32
-statement ok
-create table t (c1 int) as values (1), (2), (3), (4), (5);
-
-query ?
-select array_agg(c1) from t;
-----
-[1, 2, 3, 4, 5]
-
-statement ok
-drop table t;
-
-# array_agg_nested
-statement ok
-create table t as values (make_array([1, 2, 3], [4, 5])), (make_array([6], [7, 8])), (make_array([9]));
-
-query ?
-select array_agg(column1) from t;
-----
-[[[1, 2, 3], [4, 5]], [[6], [7, 8]], [[9]]]
-
-statement ok
-drop table t;
-
-# array_agg_ignore_nulls
-statement ok
-create table t as values (NULL, ''), (1, 'c'), (2, 'a'), (NULL, 'b'), (4, NULL), (NULL, NULL), (5, 'a');
-
-query ?
-select array_agg(column1) ignore nulls as c1 from t;
-----
-[1, 2, 4, 5]
-
-query II
-select count(*), array_length(array_agg(distinct column2) ignore nulls) from t;
-----
-7 4
-
-query ?
-select array_agg(column2 order by column1) ignore nulls from t;
-----
-[c, a, a, , b]
-
-query ?
-select array_agg(DISTINCT column2 order by column2) ignore nulls from t;
-----
-[, a, b, c]
-
-statement ok
-drop table t;
# variance_single_value
query RRRR
@@ -4927,7 +4439,6 @@ select var(sq.column1), var_pop(sq.column1), stddev(sq.column1), stddev_pop(sq.c
2 1 1.414213562373 1
-
# aggregates on empty tables
statement ok
CREATE TABLE empty (column1 bigint, column2 int);
@@ -5765,7 +5276,6 @@ DROP TABLE min_bool;
#################
-
#################
# min_max on strings/binary with null values and groups
#################
@@ -6475,7 +5985,6 @@ ORDER BY tag
426172 426172 1 426172 426172 1
-
statement ok
drop table t_source;
@@ -7441,7 +6950,6 @@ statement error
select regr_sxy(NULL, 'bar');
-
# regr_*() NULL results
query RRIRRRRRR
select regr_slope(1,1), regr_intercept(1,1), regr_count(1,1), regr_r2(1,1), regr_avgx(1,1), regr_avgy(1,1), regr_sxx(1,1), regr_syy(1,1), regr_sxy(1,1);
@@ -7469,7 +6977,6 @@ select regr_slope(column2, column1), regr_intercept(column2, column1), regr_coun
NULL NULL 3 NULL 1 4 0 8 0
-
# regr_*() basic tests
query RRIRRRRRR
select
@@ -7574,7 +7081,6 @@ b 3 0 2 1 2 6 2 18 6
c NULL NULL 1 NULL 1 10 0 0 0
-
# regr_*() testing merge_batch() from RegrAccumulator's internal implementation
statement ok
set datafusion.execution.batch_size = 1;
@@ -7634,7 +7140,6 @@ statement ok
set datafusion.execution.batch_size = 8192;
-
# regr_*() testing retract_batch() from RegrAccumulator's internal implementation
query RRIRRRRRR
SELECT
@@ -8104,13 +7609,11 @@ statement ok
drop table distinct_count_large_binary_table;
-
## Cleanup from distinct count tests
statement ok
drop table distinct_count_string_table;
-
# rule `aggregate_statistics` should not optimize MIN/MAX to wrong values on empty relation
statement ok
@@ -9133,19 +8636,6 @@ VALUES
----
x 1
-query error Error during planning: WITHIN GROUP is only supported for ordered-set aggregate functions
-SELECT array_agg(a_varchar) WITHIN GROUP (ORDER BY a_varchar)
-FROM (VALUES ('a'), ('d'), ('c'), ('a')) t(a_varchar);
-
-
-query error Error during planning: WITHIN GROUP is only supported for ordered-set aggregate functions
-SELECT array_agg(DISTINCT a_varchar) WITHIN GROUP (ORDER BY a_varchar)
-FROM (VALUES ('a'), ('d'), ('c'), ('a')) t(a_varchar);
-
-
-query error Error during planning: ORDER BY and WITHIN GROUP clauses cannot be used together in the same aggregate function
-SELECT array_agg(a_varchar order by a_varchar) WITHIN GROUP (ORDER BY a_varchar)
-FROM (VALUES ('a'), ('d'), ('c'), ('a')) t(a_varchar);
# distinct average
statement ok
diff --git a/datafusion/sqllogictest/test_files/array_agg.slt b/datafusion/sqllogictest/test_files/array_agg.slt
new file mode 100644
index 0000000000..f44e7f7d02
--- /dev/null
+++ b/datafusion/sqllogictest/test_files/array_agg.slt
@@ -0,0 +1,620 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+#######
+# Tests for the array_agg aggregate function.
+#
+# Sliding (bounded) window frames, which exercise `retract_batch`, live in
+# `array_agg_sliding_window.slt`.
+#######
+
+#######
+# Setup test data table
+#######
+statement ok
+CREATE EXTERNAL TABLE aggregate_test_100 (
+ c1 VARCHAR NOT NULL,
+ c2 TINYINT NOT NULL,
+ c3 SMALLINT NOT NULL,
+ c4 SMALLINT,
+ c5 INT,
+ c6 BIGINT NOT NULL,
+ c7 SMALLINT NOT NULL,
+ c8 INT NOT NULL,
+ c9 INT UNSIGNED NOT NULL,
+ c10 BIGINT UNSIGNED NOT NULL,
+ c11 FLOAT NOT NULL,
+ c12 DOUBLE NOT NULL,
+ c13 VARCHAR NOT NULL,
+ c14 DATE NOT NULL,
+ c15 TIMESTAMP NOT NULL,
+)
+STORED AS CSV
+LOCATION '../../testing/data/csv/aggregate_test_100_with_dates.csv'
+OPTIONS ('format.has_header' 'true');
+
+#######
+# Basic array_agg
+#######
+
+# csv_query_array_agg
+query ?
+SELECT array_agg(c13) FROM (SELECT * FROM aggregate_test_100 ORDER BY c13 LIMIT 2) test
+----
+[0VVIHzxWtNOFLtnhjHEKjXaJOSLJfm, 0keZ5G8BffGwgF2RwQD59TFzMStxCB]
+
+# csv_query_array_agg_empty
+query ?
+SELECT array_agg(c13) FROM (SELECT * FROM aggregate_test_100 LIMIT 0) test
+----
+NULL
+
+# csv_query_array_agg_one
+query ?
+SELECT array_agg(c13) FROM (SELECT * FROM aggregate_test_100 ORDER BY c13 LIMIT 1) test
+----
+[0VVIHzxWtNOFLtnhjHEKjXaJOSLJfm]
+
+# array_agg_zero
+query ?
+SELECT ARRAY_AGG([])
+----
+[[]]
+
+# array_agg_one
+query ?
+SELECT ARRAY_AGG([1])
+----
+[[1]]
+
+# test array_agg with no row qualified
+statement ok
+create table t(a int, b float, c bigint) as values (1, 1.2, 2);
+
+# returns NULL, follows DuckDB's behaviour
+query ?
+select array_agg(a) from t where a > 2;
+----
+NULL
+
+query ?
+select array_agg(b) from t where b > 3.1;
+----
+NULL
+
+query ?
+select array_agg(c) from t where c > 3;
+----
+NULL
+
+query ?I
+select array_agg(c), count(1) from t where c > 3;
+----
+NULL 0
+
+# returns 0 rows if group by is applied, follows DuckDB's behaviour
+query ?
+select array_agg(a) from t where a > 3 group by a;
+----
+
+query ?I
+select array_agg(a), count(1) from t where a > 3 group by a;
+----
+
+# returns NULL, follows DuckDB's behaviour
+query ?
+select array_agg(distinct a) from t where a > 3;
+----
+NULL
+
+query ?I
+select array_agg(distinct a), count(1) from t where a > 3;
+----
+NULL 0
+
+# returns 0 rows if group by is applied, follows DuckDB's behaviour
+query ?
+select array_agg(distinct a) from t where a > 3 group by a;
+----
+
+query ?I
+select array_agg(distinct a), count(1) from t where a > 3 group by a;
+----
+
+# test order sensitive array agg
+query ?
+select array_agg(a order by a) from t where a > 3;
+----
+NULL
+
+query ?
+select array_agg(a order by a) from t where a > 3 group by a;
+----
+
+query ?I
+select array_agg(a order by a), count(1) from t where a > 3 group by a;
+----
+
+statement ok
+drop table t;
+
+# test with no values
+statement ok
+create table t(a int, b float, c bigint);
+
+query ?
+select array_agg(a) from t;
+----
+NULL
+
+query ?
+select array_agg(b) from t;
+----
+NULL
+
+query ?
+select array_agg(c) from t;
+----
+NULL
+
+query ?I
+select array_agg(distinct a), count(1) from t;
+----
+NULL 0
+
+query ?I
+select array_agg(distinct b), count(1) from t;
+----
+NULL 0
+
+query ?I
+select array_agg(distinct b), count(1) from t;
+----
+NULL 0
+
+statement ok
+drop table t;
+
+
+# array_agg_i32
+statement ok
+create table t (c1 int) as values (1), (2), (3), (4), (5);
+
+query ?
+select array_agg(c1) from t;
+----
+[1, 2, 3, 4, 5]
+
+statement ok
+drop table t;
+
+# array_agg_nested
+statement ok
+create table t as values (make_array([1, 2, 3], [4, 5])), (make_array([6], [7, 8])), (make_array([9]));
+
+query ?
+select array_agg(column1) from t;
+----
+[[[1, 2, 3], [4, 5]], [[6], [7, 8]], [[9]]]
+
+statement ok
+drop table t;
+
+# array_agg_ignore_nulls
+statement ok
+create table t as values (NULL, ''), (1, 'c'), (2, 'a'), (NULL, 'b'), (4, NULL), (NULL, NULL), (5, 'a');
+
+query ?
+select array_agg(column1) ignore nulls as c1 from t;
+----
+[1, 2, 4, 5]
+
+query II
+select count(*), array_length(array_agg(distinct column2) ignore nulls) from t;
+----
+7 4
+
+query ?
+select array_agg(column2 order by column1) ignore nulls from t;
+----
+[c, a, a, , b]
+
+query ?
+select array_agg(DISTINCT column2 order by column2) ignore nulls from t;
+----
+[, a, b, c]
+
+statement ok
+drop table t;
+
+#######
+# array_agg with ORDER BY
+#######
+
+# array agg can use order by
+query ?
+SELECT array_agg(c13 ORDER BY c13)
+FROM
+ (SELECT *
+ FROM aggregate_test_100
+ ORDER BY c13
+ LIMIT 5) as t1
+----
+[0VVIHzxWtNOFLtnhjHEKjXaJOSLJfm, 0keZ5G8BffGwgF2RwQD59TFzMStxCB, 0og6hSkhbX8AC1ktFS4kounvTzy8Vo, 1aOcrEGd0cOqZe2I5XBOm0nDcwtBZO, 2T3wSlHdEmASmO0xcXHnndkKEt6bz8]
+
+# array agg can use order by with distinct
+query ?
+SELECT array_agg(DISTINCT c13 ORDER BY c13)
+FROM
+ (SELECT *
+ FROM aggregate_test_100
+ ORDER BY c13
+ LIMIT 5) as t1
+----
+[0VVIHzxWtNOFLtnhjHEKjXaJOSLJfm, 0keZ5G8BffGwgF2RwQD59TFzMStxCB, 0og6hSkhbX8AC1ktFS4kounvTzy8Vo, 1aOcrEGd0cOqZe2I5XBOm0nDcwtBZO, 2T3wSlHdEmASmO0xcXHnndkKEt6bz8]
+
+query error Execution error: In an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
+SELECT array_agg(DISTINCT c13 ORDER BY c12)
+FROM aggregate_test_100
+
+query error Execution error: In an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
+SELECT array_agg(DISTINCT c13 ORDER BY c13, c12)
+FROM aggregate_test_100
+
+query ?? rowsort
+with tbl as (SELECT * FROM (VALUES ('xxx', 'yyy'), ('xxx', 'yyy'), ('xxx2', 'yyy2')) AS t(x, y))
+select
+ array_agg(x order by x) as x_agg,
+ array_agg(y order by y) as y_agg
+from tbl
+group by all
+----
+[xxx, xxx, xxx2] [yyy, yyy, yyy2]
+
+query ??
+SELECT
+ (SELECT array_agg(c12 ORDER BY c12) FROM aggregate_test_100),
+ (SELECT array_agg(c13 ORDER BY c13) FROM aggregate_test_100)
+----
+[0.01479305307777301, 0.02182578039211991, 0.03968347085780355, 0.04429073092078406, 0.047343434291126085, 0.04893135681998029, 0.0494924465469434, 0.05573662213439634, 0.05636955101974106, 0.061029375346466685, 0.07260475960924484, 0.09465635123783445, 0.12357539988406441, 0.152498292971736, 0.16301110515739792, 0.1640882545084913, 0.1754261586710173, 0.17592486905979987, 0.17909035118828576, 0.18628859265874176, 0.19113293583306745, 0.2145232647388039, 0.21535402343780985, 0.2489979431 [...]
+
+query ??
+SELECT
+ array_agg(c12 ORDER BY c12),
+ array_agg(c13 ORDER BY c13)
+FROM aggregate_test_100
+----
+[0.01479305307777301, 0.02182578039211991, 0.03968347085780355, 0.04429073092078406, 0.047343434291126085, 0.04893135681998029, 0.0494924465469434, 0.05573662213439634, 0.05636955101974106, 0.061029375346466685, 0.07260475960924484, 0.09465635123783445, 0.12357539988406441, 0.152498292971736, 0.16301110515739792, 0.1640882545084913, 0.1754261586710173, 0.17592486905979987, 0.17909035118828576, 0.18628859265874176, 0.19113293583306745, 0.2145232647388039, 0.21535402343780985, 0.2489979431 [...]
+
+query ?? rowsort
+with tbl as (SELECT * FROM (VALUES ('xxx', 'yyy'), ('xxx', 'yyy'), ('xxx2', 'yyy2')) AS t(x, y))
+select
+ array_agg(distinct x order by x) as x_agg,
+ array_agg(distinct y order by y) as y_agg
+from tbl
+group by all
+----
+[xxx, xxx2] [yyy, yyy2]
+
+query ??
+SELECT
+ (SELECT array_agg(DISTINCT c12 ORDER BY c12) FROM aggregate_test_100),
+ (SELECT array_agg(DISTINCT c13 ORDER BY c13) FROM aggregate_test_100)
+----
+[0.01479305307777301, 0.02182578039211991, 0.03968347085780355, 0.04429073092078406, 0.047343434291126085, 0.04893135681998029, 0.0494924465469434, 0.05573662213439634, 0.05636955101974106, 0.061029375346466685, 0.07260475960924484, 0.09465635123783445, 0.12357539988406441, 0.152498292971736, 0.16301110515739792, 0.1640882545084913, 0.1754261586710173, 0.17592486905979987, 0.17909035118828576, 0.18628859265874176, 0.19113293583306745, 0.2145232647388039, 0.21535402343780985, 0.2489979431 [...]
+
+query ??
+SELECT
+ array_agg(DISTINCT c12 ORDER BY c12),
+ array_agg(DISTINCT c13 ORDER BY c13)
+FROM aggregate_test_100
+----
+[0.01479305307777301, 0.02182578039211991, 0.03968347085780355, 0.04429073092078406, 0.047343434291126085, 0.04893135681998029, 0.0494924465469434, 0.05573662213439634, 0.05636955101974106, 0.061029375346466685, 0.07260475960924484, 0.09465635123783445, 0.12357539988406441, 0.152498292971736, 0.16301110515739792, 0.1640882545084913, 0.1754261586710173, 0.17592486905979987, 0.17909035118828576, 0.18628859265874176, 0.19113293583306745, 0.2145232647388039, 0.21535402343780985, 0.2489979431 [...]
+
+statement ok
+CREATE EXTERNAL TABLE agg_order (
+c1 INT NOT NULL,
+c2 INT NOT NULL,
+c3 INT NOT NULL
+)
+STORED AS CSV
+LOCATION '../core/tests/data/aggregate_agg_multi_order.csv'
+OPTIONS ('format.has_header' 'true');
+
+# test array_agg with order by multiple columns
+query ?
+select array_agg(c1 order by c2 desc, c3) from agg_order;
+----
+[5, 6, 7, 8, 9, 1, 2, 3, 4, 10]
+
+query TT
+explain select array_agg(c1 order by c2 desc, c3) from agg_order;
+----
+logical_plan
+01)Aggregate: groupBy=[[]], aggr=[[array_agg(agg_order.c1) ORDER BY [agg_order.c2 DESC NULLS FIRST, agg_order.c3 ASC NULLS LAST]]]
+02)--TableScan: agg_order projection=[c1, c2, c3]
+physical_plan
+01)AggregateExec: mode=Final, gby=[], aggr=[array_agg(agg_order.c1) ORDER BY [agg_order.c2 DESC NULLS FIRST, agg_order.c3 ASC NULLS LAST]]
+02)--CoalescePartitionsExec
+03)----AggregateExec: mode=Partial, gby=[], aggr=[array_agg(agg_order.c1) ORDER BY [agg_order.c2 DESC NULLS FIRST, agg_order.c3 ASC NULLS LAST]]
+04)------SortExec: expr=[c2@1 DESC, c3@2 ASC NULLS LAST], preserve_partitioning=[true]
+05)--------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
+06)----------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/core/tests/data/aggregate_agg_multi_order.csv]]}, projection=[c1, c2, c3], file_type=csv, has_header=true
+
+# Regression test: ARRAY_AGG with conflicting ASC/DESC ORDER BY in the same query.
+# get_finer_aggregate_exprs_requirement picks ASC as the common requirement and
+# reverses the DESC aggregate (is_reversed=true, ordering_req=[ASC]).
+# The optimizer then sets is_input_pre_ordered=true on both. Without the fix,
+# state() emits values reversed to DESC but ordering keys still in ASC order,
+# causing merge_batch to pair each value with the wrong key (silent wrong results).
+query TT
+explain select array_agg(c1 order by c1), array_agg(c1 order by c1 desc) from agg_order;
+----
+logical_plan
+01)Aggregate: groupBy=[[]], aggr=[[array_agg(agg_order.c1) ORDER BY [agg_order.c1 ASC NULLS LAST], array_agg(agg_order.c1) ORDER BY [agg_order.c1 DESC NULLS FIRST]]]
+02)--TableScan: agg_order projection=[c1]
+physical_plan
+01)AggregateExec: mode=Final, gby=[], aggr=[array_agg(agg_order.c1) ORDER BY [agg_order.c1 ASC NULLS LAST], array_agg(agg_order.c1) ORDER BY [agg_order.c1 DESC NULLS FIRST]]
+02)--CoalescePartitionsExec
+03)----AggregateExec: mode=Partial, gby=[], aggr=[array_agg(agg_order.c1) ORDER BY [agg_order.c1 ASC NULLS LAST], array_agg(agg_order.c1) ORDER BY [agg_order.c1 DESC NULLS FIRST]]
+04)------SortExec: expr=[c1@0 ASC NULLS LAST], preserve_partitioning=[true]
+05)--------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
+06)----------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/core/tests/data/aggregate_agg_multi_order.csv]]}, projection=[c1], file_type=csv, has_header=true
+
+query ??
+select array_agg(c1 order by c1), array_agg(c1 order by c1 desc) from agg_order;
+----
+[1, 2, 3, 4, 5, 6, 7, 8, 9, 10] [10, 9, 8, 7, 6, 5, 4, 3, 2, 1]
+
+# test array_agg_order with list data type
+statement ok
+CREATE TABLE array_agg_order_list_table AS VALUES
+ ('w', 2, [1,2,3], 10),
+ ('w', 1, [9,5,2], 20),
+ ('w', 1, [3,2,5], 30),
+ ('b', 2, [4,5,6], 20),
+ ('b', 1, [7,8,9], 30)
+;
+
+query T? rowsort
+select column1, array_agg(column3 order by column2, column4 desc) from array_agg_order_list_table group by column1;
+----
+b [[7, 8, 9], [4, 5, 6]]
+w [[3, 2, 5], [9, 5, 2], [1, 2, 3]]
+
+query T?? rowsort
+select column1, first_value(column3 order by column2, column4 desc), last_value(column3 order by column2, column4 desc) from array_agg_order_list_table group by column1;
+----
+b [7, 8, 9] [4, 5, 6]
+w [3, 2, 5] [1, 2, 3]
+
+query T? rowsort
+select column1, nth_value(column3, 2 order by column2, column4 desc) from array_agg_order_list_table group by column1;
+----
+b [4, 5, 6]
+w [9, 5, 2]
+
+query ?
+select array_agg(DISTINCT column2 order by column2) from array_agg_order_list_table;
+----
+[1, 2]
+
+query ?
+select array_agg(DISTINCT column2 order by column2 desc) from array_agg_order_list_table;
+----
+[2, 1]
+
+query ?
+select array_agg(DISTINCT column2 + 1 order by column2 + 1 desc) from array_agg_order_list_table;
+----
+[3, 2]
+
+query ?
+select array_agg(DISTINCT column2 order by column2) from array_agg_order_list_table GROUP BY column1;
+----
+[1, 2]
+[1, 2]
+
+statement error In an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
+select array_agg(DISTINCT column2 order by column1) from array_agg_order_list_table;
+
+statement ok
+drop table array_agg_order_list_table;
+
+#######
+# array_agg with DISTINCT
+#######
+
+# select with count to forces array_agg_distinct function, since single distinct expression is converted to group by by optimizer
+# csv_query_array_agg_distinct
+query ?I
+SELECT array_sort(array_agg(distinct c2)), count(1) FROM aggregate_test_100
+----
+[1, 2, 3, 4, 5] 100
+
+# test array_agg_distinct with list data type
+statement ok
+CREATE TABLE array_agg_distinct_list_table AS VALUES
+ ('w', [0,1]),
+ ('w', [0,1]),
+ ('w', [1,0]),
+ ('b', [1,0]),
+ ('b', [1,0]),
+ ('b', [1,0]),
+ ('b', [0,1]),
+ (NULL, [0,1]),
+ ('b', NULL)
+;
+
+# Apply array_sort to have deterministic result, higher dimension nested array also works but not for array sort,
+# so they are covered in `datafusion/functions-aggregate/src/array_agg.rs`
+query ??
+select array_sort(c1), array_sort(c2) from (
+ select array_agg(distinct column1) as c1, array_agg(distinct column2) ignore nulls as c2 from array_agg_distinct_list_table
+);
+----
+[NULL, b, w] [[0, 1], [1, 0]]
+
+statement ok
+drop table array_agg_distinct_list_table;
+
+
+# Test array_agg with DISTINCT and IGNORE NULLS (regression test for issue #19735)
+query ?
+SELECT array_sort(ARRAY_AGG(DISTINCT x IGNORE NULLS)) as result
+FROM (VALUES (1), (2), (NULL), (2), (NULL), (1)) AS t(x);
+----
+[1, 2]
+
+# Test distinct aggregate function with merge batch
+query II
+with A as (
+ select 1 as id, 2 as foo
+ UNION ALL
+ select 1, null
+ UNION ALL
+ select 1, null
+ UNION ALL
+ select 1, 3
+ UNION ALL
+ select 1, 2
+ ---- The order is non-deterministic, verify with length
+) select array_length(array_agg(distinct a.foo)), sum(distinct 1) from A a group by a.id;
+----
+3 1
+
+# It has only AggregateExec with FinalPartitioned mode, so `merge_batch` is used
+# If the plan is changed, whether the `merge_batch` is used should be verified to ensure the test coverage
+query TT
+explain with A as (
+ select 1 as id, 2 as foo
+ UNION ALL
+ select 1, null
+ UNION ALL
+ select 1, null
+ UNION ALL
+ select 1, 3
+ UNION ALL
+ select 1, 2
+) select array_length(array_agg(distinct a.foo)), sum(distinct 1) from A a group by a.id;
+----
+logical_plan
+01)Projection: array_length(array_agg(DISTINCT a.foo)), sum(DISTINCT Int64(1))
+02)--Aggregate: groupBy=[[a.id]], aggr=[[array_agg(DISTINCT a.foo), sum(DISTINCT Int64(1))]]
+03)----SubqueryAlias: a
+04)------SubqueryAlias: a
+05)--------Union
+06)----------Projection: Int64(1) AS id, Int64(2) AS foo
+07)------------EmptyRelation: rows=1
+08)----------Projection: Int64(1) AS id, Int64(NULL) AS foo
+09)------------EmptyRelation: rows=1
+10)----------Projection: Int64(1) AS id, Int64(NULL) AS foo
+11)------------EmptyRelation: rows=1
+12)----------Projection: Int64(1) AS id, Int64(3) AS foo
+13)------------EmptyRelation: rows=1
+14)----------Projection: Int64(1) AS id, Int64(2) AS foo
+15)------------EmptyRelation: rows=1
+physical_plan
+01)ProjectionExec: expr=[array_length(array_agg(DISTINCT a.foo)@1) as array_length(array_agg(DISTINCT a.foo)), sum(DISTINCT Int64(1))@2 as sum(DISTINCT Int64(1))]
+02)--AggregateExec: mode=FinalPartitioned, gby=[id@0 as id], aggr=[array_agg(DISTINCT a.foo), sum(DISTINCT Int64(1))], ordering_mode=Sorted
+03)----RepartitionExec: partitioning=Hash([id@0], 4), input_partitions=5
+04)------AggregateExec: mode=Partial, gby=[id@0 as id], aggr=[array_agg(DISTINCT a.foo), sum(DISTINCT Int64(1))], ordering_mode=Sorted
+05)--------UnionExec
+06)----------ProjectionExec: expr=[1 as id, 2 as foo]
+07)------------PlaceholderRowExec
+08)----------ProjectionExec: expr=[1 as id, NULL as foo]
+09)------------PlaceholderRowExec
+10)----------ProjectionExec: expr=[1 as id, NULL as foo]
+11)------------PlaceholderRowExec
+12)----------ProjectionExec: expr=[1 as id, 3 as foo]
+13)------------PlaceholderRowExec
+14)----------ProjectionExec: expr=[1 as id, 2 as foo]
+15)------------PlaceholderRowExec
+
+#######
+# Unsupported syntax
+#######
+
+statement error This feature is not implemented: Calling array_agg: LIMIT not supported in function arguments: 1
+SELECT array_agg(c13 LIMIT 1) FROM aggregate_test_100
+
+query error Error during planning: WITHIN GROUP is only supported for ordered-set aggregate functions
+SELECT array_agg(a_varchar) WITHIN GROUP (ORDER BY a_varchar)
+FROM (VALUES ('a'), ('d'), ('c'), ('a')) t(a_varchar);
+
+
+query error Error during planning: WITHIN GROUP is only supported for ordered-set aggregate functions
+SELECT array_agg(DISTINCT a_varchar) WITHIN GROUP (ORDER BY a_varchar)
+FROM (VALUES ('a'), ('d'), ('c'), ('a')) t(a_varchar);
+
+
+query error Error during planning: ORDER BY and WITHIN GROUP clauses cannot be used together in the same aggregate function
+SELECT array_agg(a_varchar order by a_varchar) WITHIN GROUP (ORDER BY a_varchar)
+FROM (VALUES ('a'), ('d'), ('c'), ('a')) t(a_varchar);
+
+# test array_agg_distinct with dictionary encoded data
+statement ok
+CREATE TABLE array_agg_distinct_dict_table AS VALUES
+ ('w', 1),
+ ('w', 1),
+ ('b', 2),
+ ('b', 1),
+ (NULL, 2)
+;
+
+# Apply array_sort to have deterministic result
+query ??
+select array_sort(c1), array_sort(c2) from (
+ select array_agg(distinct arrow_cast(column1, 'Dictionary(Int32, Utf8)')) as c1,
+ array_agg(distinct arrow_cast(column2, 'Dictionary(Int8, Int64)')) ignore nulls as c2
+ from array_agg_distinct_dict_table
+);
+----
+[NULL, b, w] [1, 2]
+
+# The element type of the returned list must stay dictionary encoded, otherwise the
+# aggregate output does not match the schema it declared
+query T
+select arrow_typeof(array_agg(distinct arrow_cast(column1, 'Dictionary(Int32, Utf8)')))
+from array_agg_distinct_dict_table;
+----
+List(Dictionary(Int32, Utf8))
+
+# ... including when the dictionary is nested inside another type
+query ?
+select array_sort(c) from (
+ select array_agg(distinct struct(arrow_cast(column1, 'Dictionary(Int32, Utf8)') as f)) as c
+ from array_agg_distinct_dict_table
+);
+----
+[{f: NULL}, {f: b}, {f: w}]
+
+# ... and when no rows are aggregated at all
+query ?
+select array_agg(distinct arrow_cast(column1, 'Dictionary(Int32, Utf8)'))
+from array_agg_distinct_dict_table where column2 > 100;
+----
+NULL
+
+query T
+select arrow_typeof(array_agg(distinct arrow_cast(column1, 'Dictionary(Int32, Utf8)')))
+from array_agg_distinct_dict_table where column2 > 100;
+----
+List(Dictionary(Int32, Utf8))
+
+statement ok
+drop table array_agg_distinct_dict_table;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org