Aggregation function arbitrary should be order sensitive about presto

[amitkdutta]

CC: @tdcmeehan @aditi-pandit @majetideepak

[mbasmanova]

CC: @feilong-liu @mlyublena @kaikalur

[AaronGhebretinsae]

it seems like the 6th parameter is marked false when it should be true. is that the only problem? or am I misunderstanding the issue?

[mbasmanova]

it seems like the 6th parameter is marked false when it should be true. is that the only problem? or am I misunderstanding the issue?

This is the only issue.

[feilong-liu]

Should return same result across multiple runs.

Why it should return the same result? arbitrary is supposed to be ok to return any values right?

[mbasmanova]

@feilong-liu The expectation is that arbitrary(x ORDER BY x) would always return same result, no?

[feilong-liu]

@feilong-liu The expectation is that arbitrary(x ORDER BY x) would always return same result, no?

Not sure about this. I think when the user use arbitrary function, it already specifies the intention that an arbitrary value is good.

[kaikalur]

@feilong-liu The expectation is that arbitrary(x ORDER BY x) would always return same result, no?

No arbitrary definitely does not return the same result. ORDER BY is done in a very hacky way because its expensive (prevents partial agg :() so we do some filtering in the plan to say certain operations are not affected by order by (in fact we use allowlist). So only things like array_agg etc. respect it.

[kaikalur]

see:

https://github.com/prestodb/presto/blob/master/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/PruneOrderByInAggregation.java#L63

That uses implementation property to do planning!

[mbasmanova]

Here is what Presto documentation says:

https://prestodb.io/docs/current/functions/aggregate.html

"Some aggregate functions such as array_agg() produce different results depending on the order of input values. This ordering can be specified by writing an ORDER BY Clause within the aggregate function:"

My reading of this statement suggests that any function whose results depend on order of inputs (arbitrary, min_by, max_by, etc.) can be forced to return same result if we add an ORDER BY.

Furthermore, I understand that evaluating aggregate functions on sorted inputs is expensive and it makes sense to skip ORDER BY if it is not needed. For example, the results of min/max don't depend on whether inputs are sorted, hence, it is safe to drop ORDER BY. However, the results of min_by, max_by, arbitrary do depend on the order of inputs, hence, it is not safe to drop ORDER BY.

[atikkk]

Can you help me to setup project locally

[mbasmanova]

Can you help me to setup project locally

@tdcmeehan @majetideepak Tim, Deepak, do we have instructions somewhere?

[tdcmeehan]

@atikkk please read through CONTRIBUTING. If you have any questions please ask in #dev in our Slack channel (found from prestodb.io).