Skip to content

Commit 90435f6

Browse files
adriangbjayzhan211claude
committed
test: spilled aggregates over wide states in sqllogictest
A string_agg over 40000 groups of 200 or 600 byte values fails with ResourcesExhausted under a 2 MB limit. The aggregate spills runs of 1 MiB batches, and the smallest merge of two runs does not fit in what the aggregate held. This commit adds the aggregate queries of apache#26987 to spill_batch_bound.slt. They pass on main and guard against regressions: array_agg over a few large groups (apache#25851), string_agg at 4 MB, and the legacy aggregate stream. It also adds the two 2 MB queries, recorded with the error of main. Co-authored-by: Jay Zhan <[email protected]> Co-Authored-By: Claude Opus 5.5 <[email protected]>
1 parent a485923 commit 90435f6

1 file changed

Lines changed: 76 additions & 1 deletion

File tree

‎datafusion/sqllogictest/test_files/spill_batch_bound.slt‎

Lines changed: 76 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
# SPDX-FileCopyrightText: See the NOTICE file distributed with this work for additional information regarding copyright ownership
33
# SPDX-FileContributor: Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements
44

5-
# Spilled sorts over wide rows.
5+
# Spilled sorts and aggregates over wide rows.
66
#
77
# Each query below spills rows much wider than `batch_size` rows of narrow
88
# data, under a memory limit that fits each row but not a whole batch of them.
@@ -61,6 +61,81 @@ RESET datafusion.execution.sort_spill_reservation_bytes
6161
statement ok
6262
RESET datafusion.execution.batch_size
6363

64+
# --- Aggregate: a few groups with large states (#25851) ---
65+
66+
statement ok
67+
SET datafusion.execution.target_partitions = 2
68+
69+
statement ok
70+
SET datafusion.runtime.memory_limit = '12M'
71+
72+
query II
73+
SELECT count(*) AS groups, sum(n) AS values_collected FROM (
74+
SELECT k, cardinality(array_agg(v)) AS n
75+
FROM (
76+
SELECT value % 4 AS k, concat(CAST(value AS VARCHAR), repeat('x', 100)) AS v
77+
FROM generate_series(1, 200000)
78+
)
79+
GROUP BY k
80+
)
81+
----
82+
4 200000
83+
84+
statement ok
85+
SET datafusion.execution.target_partitions = 1
86+
87+
# --- Aggregate: many groups whose states are wide relative to the pool ---
88+
89+
statement ok
90+
SET datafusion.runtime.memory_limit = '4M'
91+
92+
query II
93+
SELECT count(*), sum(length(s)) FROM (
94+
SELECT k, string_agg(v, ',') AS s
95+
FROM (SELECT value % 40000 AS k, repeat('x', 600) AS v FROM generate_series(1, 160000))
96+
GROUP BY k
97+
)
98+
----
99+
40000 96120000
100+
101+
# The same groups under a 2 MB limit, where the smallest merge of two runs of
102+
# 1 MiB batches does not fit in what the aggregate held.
103+
statement ok
104+
SET datafusion.runtime.memory_limit = '2M'
105+
106+
query error Resources exhausted
107+
SELECT count(*), sum(length(s)) FROM (
108+
SELECT k, string_agg(v, ',') AS s
109+
FROM (SELECT value % 40000 AS k, repeat('x', 200) AS v FROM generate_series(1, 160000))
110+
GROUP BY k
111+
)
112+
113+
query error Resources exhausted
114+
SELECT count(*), sum(length(s)) FROM (
115+
SELECT k, string_agg(v, ',') AS s
116+
FROM (SELECT value % 40000 AS k, repeat('x', 600) AS v FROM generate_series(1, 160000))
117+
GROUP BY k
118+
)
119+
120+
# The legacy aggregate stream.
121+
statement ok
122+
SET datafusion.execution.enable_migration_aggregate = false
123+
124+
statement ok
125+
SET datafusion.runtime.memory_limit = '4M'
126+
127+
query II
128+
SELECT count(*), sum(length(s)) FROM (
129+
SELECT k, string_agg(v, ',') AS s
130+
FROM (SELECT value % 40000 AS k, repeat('x', 200) AS v FROM generate_series(1, 160000))
131+
GROUP BY k
132+
)
133+
----
134+
40000 32120000
135+
136+
statement ok
137+
RESET datafusion.execution.enable_migration_aggregate
138+
64139
# Restore settings to slt runner defaults
65140
statement ok
66141
SET datafusion.execution.target_partitions = 4

0 commit comments

Comments
 (0)