## Which issue does this PR close?
Closes#18109.
## Rationale for this change
Previously, the SQL planner accepted `WITHIN GROUP` clauses for all
aggregate UDAFs, even those that did not explicitly support ordered-set
semantics. This behavior was too permissive and inconsistent with
PostgreSQL. For example, queries such as `SUM(x) WITHIN GROUP (ORDER BY
x)` were allowed, even though `SUM` is not an ordered-set aggregate.
This PR enforces stricter validation so that only UDAFs that explicitly
return `true` from `supports_within_group_clause()` may use `WITHIN
GROUP`. All other aggregates now produce a clear planner error when this
syntax is used.
## What changes are included in this PR?
* Added type alias `WithinGroupExtraction` to simplify complex tuple
return types used by helper functions.
* Introduced a new helper method `extract_and_prepend_within_group_args`
to centralize logic for handling `WITHIN GROUP` argument rewriting.
* Updated the planner to:
* Validate that only UDAFs with `supports_within_group_clause()` can
accept `WITHIN GROUP`.
* Prepend `WITHIN GROUP` ordering expressions to function arguments only
for supported ordered-set aggregates.
* Produce clear error messages when `WITHIN GROUP` is used incorrectly.
* Added comprehensive unit tests verifying correct behavior and failure
cases:
* `WITHIN GROUP` rejected for non-ordered-set aggregates (`MIN`, `SUM`,
etc.).
* `WITHIN GROUP` accepted for ordered-set aggregates such as
`percentile_cont`.
* Validation for named arguments, multiple ordering expressions, and
semantic conflicts with `OVER` clauses.
* Updated SQL logic tests (`aggregate.slt`) to reflect new rejection
behavior.
* Updated documentation:
* `aggregate_functions.md` and developer docs to clarify when and how
`WITHIN GROUP` can be used.
* `upgrading.md` to inform users of this stricter enforcement and
migration guidance.
## Are these changes tested?
✅ Yes.
* New tests in `sql_integration.rs` validate acceptance, rejection, and
argument behavior of `WITHIN GROUP` for both valid and invalid cases.
* SQL logic tests (`aggregate.slt`) include negative test cases
confirming planner rejections.
## Are there any user-facing changes?
✅ Yes.
* Users attempting to use `WITHIN GROUP` with regular aggregates (e.g.
`SUM`, `AVG`, `MIN`, `MAX`) will now see a planner error:
> `WITHIN GROUP is only supported for ordered-set aggregate functions`
* Documentation has been updated to clearly describe `WITHIN GROUP`
semantics and provide examples of valid and invalid usage.
No API-breaking changes were introduced; only stricter planner
validation and improved error messaging.
## Summary
Adds exact `percentile_cont` aggregate function as the counterpart to
the existing `approx_percentile_cont` function.
## What changes were made?
### New Implementation
- Created `percentile_cont.rs` with full implementation
- `PercentileCont` struct implementing `AggregateUDFImpl`
- `PercentileContAccumulator` for standard aggregation
- `DistinctPercentileContAccumulator` for DISTINCT mode
- `PercentileContGroupsAccumulator` for efficient grouped aggregation
- `calculate_percentile` function with linear interpolation
### Features
- **Exact calculation**: Stores all values in memory for precise results
- **WITHIN GROUP syntax**: Supports `WITHIN GROUP (ORDER BY ...)`
- **Interpolation**: Uses linear interpolation between values
- **All numeric types**: Works with integers, floats, and decimals
- **Ordered-set aggregate**: Properly marked as
`is_ordered_set_aggregate()`
- **GROUP BY support**: Efficient grouped aggregation via
GroupsAccumulator
### Tests
Added comprehensive tests in `aggregate.slt`:
- Error conditions validation
- Basic percentile calculations (0.0, 0.25, 0.5, 0.75, 1.0)
- Comparison with `median` function
- Ascending and descending order
- GROUP BY aggregation
- NULL handling
- Edge cases (empty sets, single values)
- Float interpolation
- Various numeric data types
## Example Usage
```sql
-- Basic usage with WITHIN GROUP syntax
SELECT percentile_cont(0.75) WITHIN GROUP (ORDER BY column_name)
FROM table_name;
-- With GROUP BY
SELECT category, percentile_cont(0.95) WITHIN GROUP (ORDER BY value)
FROM sales
GROUP BY category;
-- Compare with median (percentile_cont(0.5) == median)
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY price) FROM products;
```
## Performance Considerations
Like `median`, this function stores all values in memory before
computing results. For large datasets or when approximation is
acceptable, use `approx_percentile_cont` instead.
## Related Issues
Closes#6714🤖 Generated with [Claude Code](https://claude.com/claude-code)
---------
Co-authored-by: Claude <noreply@anthropic.com>
* feat: Support `FILTER` clause in aggregate window functions
* fix: Box `WindowFunction` in `ExprFuncKind` enum to reduce enum total size
As suggested by `clippy`:
```
warning: large size difference between variants
--> datafusion/expr/src/expr_fn.rs:772:1
|
772 | / pub enum ExprFuncKind {
773 | | Aggregate(AggregateFunction),
| | ---------------------------- the second-largest variant contains at least 72 bytes
774 | | Window(WindowFunction),
| | ---------------------- the largest variant contains at least 288 bytes
775 | | }
| |_^ the entire enum is at least 288 bytes
|
= help: for further information visit https://rust-lang.github.io/rust-clippy/master/index.html#large_enum_variant
= note: `#[warn(clippy::large_enum_variant)]` on by default
help: consider boxing the large fields to reduce the total size of the enum
|
774 - Window(WindowFunction),
774 + Window(Box<WindowFunction>),
|
```
* test: Add DataFrame API test for FILTER clause on aggregate window functions
* docs: Update aggregate and window function documentation with FILTER support
* docs: Link missing proto fields to github issue in TODO comment
* Add sqllogictests
* Allow both new and old sytanx for approx_percentile_cont and approx_percentile_cont_with_weight
* Update docs
* Add documentation and more tests
* Support centroids config for `approx_percentile_cont_with_weight`
* Match two functions' signature
* Update docs
* Address comments and unify centroids config
* Add within group variable to aggregate function and arguments
* Support within group and disable null handling for ordered set aggregate functions (#13511)
* Refactored function to match updated signature
* Modify proto to support within group clause
* Modify physical planner and accumulator to support ordered set aggregate function
* Support session management for ordered set aggregate functions
* Align code, tests, and examples with changes to aggregate function logic
* Ensure compatibility with new `within_group` and `order_by` handling.
* Adjust tests and examples to align with the new logic.
* Fix typo in existing comments
* Enhance test
* Add test cases for changed signature
* Update signature in docs
* Fix bug : handle missing within_group when applying children tree node
* Change the signature of approx_percentile_cont for consistency
* Add missing within_group for expr display
* Handle edge case when over and within group clause are used together
* Apply clippy advice: avoids too many arguments
* Add new test cases using descending order
* Apply cargo fmt
* Revert unintended submodule changes
* Apply prettier guidance
* Apply doc guidance by update_function_doc.sh
* Rollback WITHIN GROUP and related logic after converting it into expr
* Make it not to handle redundant logic
* Rollback ordered set aggregate functions from session to save same info in udf itself
* Convert within group to order by when converting sql to expr
* Add function to determine it is ordered-set aggregate function
* Rollback within group from proto
* Utilize within group as order by in functions-aggregate
* Apply clippy
* Convert order by to within group
* Apply cargo fmt
* Remove plain line breaks
* Remove duplicated column arg in schema name
* Refactor boolean functions to just return primitive type
* Make within group necessary in the signature of existing ordered set aggr funcs
* Apply cargo fmt
* Support a single ordering expression in the signature
* Apply cargo fmt
* Add dataframe function test cases to verify descending ordering
* Apply cargo fmt
* Apply code reviews
* Uses order by consistently after done with sql
* Remove redundant comment
* Serve more clear error msg
* Handle error cases in the same code block
* Update error msg in test as corresponding code changed
* fix
---------
Co-authored-by: Jay Zhan <jayzhan211@gmail.com>
* Naive test pass
i
* Add new tests and simplifications
* move tests to the .slt file
* update requirement
* update tests
* Add support for partiallyOrdered aggregation sensitive.
* Resolve linter errors
* update comments
* minor changes
* retract changes in generated
* update proto files
* Simplifications
* Make types consistent in schema, and data
* Update todos
* Convert API to vector
* Convert get_finest to handle Vector inputs
* simplifications, update comment
* initial commit, add test
* Add support for FIRST Aggregate function.
* Add support for last aggregate
* Update cargo.lock
* Remove distinct, and limit from First and last aggregate.
* Add reverse for First and Last Aggregator
* Update cargo lock
* Minor code simplifications
* Update comment
* Update documents
* Fix projection pushdown bug
* fix projection push down failure bug
* combine first_agg and last_agg parsers
* Update documentation
* Update subproject
* initial commit
* Add test code
* initial version
* simplify prints
* minor changes
* sqllogictests pass
* All tests pass
* update proto function names
* Minor changes
* do not consider ordering requirement in ordering insensitive aggregators
* Reject aggregate order by for window functions.
* simplifications
* Fix cargo lock file
* Update comment
* Rename aggregator first and last
* minor change
* Comment improvements
* Remove count from First,Last accumulators
* Address reviews
* Remove camel to upper snake util, make aggregate function names explicit
* update the test
---------
Co-authored-by: Mehmet Ozan Kabak <ozankabak@gmail.com>
Co-authored-by: berkaysynnada <berkay.sahin@synnada.ai>
* chore: update sql function documentation
* chore: remove sql syntax hl from sql function sigs
* chore: ran prettier on modified markdown
* chore: update sql function docs to address PR review
* chore: added arrow_cast documenation, mention unicode support in chr
kosiew