matrixorigin / matrixorigin/matrixone
[Bug]: Ordered-set percentiles ignore agg_spill_mem in multi-CN execution
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
# [Bug]: Ordered-set percentiles ignore `agg_spill_mem` in multi-CN execution
## Summary
`percentile_cont` and `percentile_disc` retain their ordered state in memory instead of entering the configured spill path. This was reproduced by the feature #25144 R059 Nightly test on two MatrixOne `main` commits. The result values remain correct, but `agg_spill_mem` is not honored by the ordered-set percentile execution path.
## Environment
- MatrixOne: `main` commit `1d7d425f6cd955d670cbd04b5b49ab7b682206d6`
- Nightly script: `f5da0bd6072a59ae1f796494c7e2ad29358a5c1a`
- TKE: 3 Working CNs, multi-CN physical plan (`14 core`)
- Data: 10,000,000 rows, `BIGINT` value column, one logical group
- Session: `agg_spill_mem=2097152` (2 MiB)
- Direct run: https://github.com/matrixorigin/mo-nightly-regression/actions/runs/34968829037
- Artifact: `issue-25144-ordered-set-percentile-1d7d425-34968829037-1` (ID `10397475709`, SHA-256 `7c7486a3db305d694bc07ceaa919f7e94f432670a952cd603353115741f4eb84`)
## Reproduction
```sql
create database pct_spill;
use pct_spill;
create table t(id bigint primary key, v bigint);
insert into t
select result, result from generate_series(1, 10000000) as gs;
set session agg_spill_mem = 2097152;
explain analyze
select percentile_cont(0.95) within group (order by v),
percentile_disc(0.95) within group (order by v)
from t;
```
## Actual
The R059 query returns the correct values but the plan reports no spill. Its Aggregate consumed 1.03 GiB—more than 500× the configured threshold—and contains neither `SpillRows` nor `SpillSize`.
```text
Aggregate
inputRows=10000000 outputRows=1
InputSize=76.29 MiB OutputSize=16 bytes
MemorySize=1.03 GiB
Aggregate Functions: percentile_cont(0.95) ..., percentile_disc(0.95) ...
```
The machine-readable report records `spill_rows=0`, `elapsed_seconds=17.922017`, and `AssertionError: forced spill plan did not report SpillRows`.
The same R059 assertion also failed on MatrixOne `main` `b347a62a30719cf3f2c8ca5a2da0dcfb11639f8c`; that earlier run did not retain its Explain, while the latest run does.
## Expected
With a 2 MiB `agg_spill_mem` limit, ordered-set percentile state should use its bounded spill path. `EXPLAIN ANALYZE` should contain positive `SpillRows` and `SpillSize`, while preserving the percentile results. Retained aggregate memory should not grow to 1.03 GiB.
## Impact
Large ordered-set percentile queries can retain substantially more memory than the session spill limit, risking CN memory pressure. Feature design case `PCT-R059` cannot be marked passed.
## Scope and related issue
This is not a duplicate of #28654: that issue concerns ordered `GROUP_CONCAT`. Both symptoms involve `agg_spill_mem` and ordered aggregate state after allocation-account changes, so the implementation path may be related, but this reproducer exercises `percentile_cont`/`percentile_disc` specifically.
Contributor guide
Assessment
This issue has not been assessed yet.