prestodb/presto

Aggregation function arbitrary should be order sensitive

Open

#21,786 opened on Jan 25, 2024

View on GitHub
 (13 comments) (1 reaction) (0 assignees)Java (5,240 forks)batch import
buggood first issue

Repository metrics

Stars
 (15,558 stars)
PR merge metrics
 (Avg merge 34d 14h) (120 merged PRs in 30d)

Description

Hi , We have found that arbitary aggregate function is order sensitive as the resultant value can change across runs. This happens despite setting ORDER BY as it doesnt honor the order sensitivity flag.

Aggregation functions that are marked as order sensitive allows user to use ORDER BY within the aggregation function call to specify an order of input. (https://prestodb.io/docs/current/functions/aggregate.html#functions-aggregate--page-root).

Your Environment

N/A

Expected Behavior

SELECT ARBITRARY(x ORDER BY x) from SOME_LARGE_TABLE; -- This table has column x

Should return same result across multiple runs.

Current Behavior

The above should ideally always return the same result , however the value returned is indeterminate.

Possible Solution

The arbitary aggregate function can be marked as order sensitive as shown below:

https://github.com/prestodb/presto/blob/4b458d41b8988f02c09bf9ff85e5d7bacf093b98/presto-main/src/main/java/com/facebook/presto/operator/aggregation/ArbitraryAggregationFunction.java#L171

Context

The Velox team is trying to ensure correctness of its aggregate functions, Because Presto's arbitary is not marked order_sensitive its harder to validate its Velox equivalent.

Contributor guide

Research direction
Inspect the ArbitraryAggregationFunction class at the given line and ensure it implements the OrderSensitive interface. Then add the order sensitive flag to the aggregation metadata.
Tech stack
javasql
Domain
backend
Issue type
Bug
DifficultyEstimated implementation difficulty for a new contributor, from 1 for very small changes to 5 for expert-level work.
2
Estimated timeA rough time range for an experienced contributor to investigate, implement, test, and prepare a pull request.
1-3 hours
Activity statusHow available the issue appears right now: fresh, active, stale, blocked, or waiting on maintainer input.
Active
ClarityHow clearly the issue explains the expected change, acceptance criteria, and next step.
Clear
Prerequisites
JavaSQL
Newbie friendlinessA 1-100 score estimating how approachable this issue is for first-time contributors.
70