Describe the bug
Calling an order-insensitive aggregate with an ORDER BY fails in a grouped query whenever the plan runs in two phases (Partial → FinalPartitioned), as it does with the default target_partitions. The ORDER BY should be ignored, as it already is for sum and count.
There are two failure modes, with different causes.
1. min and max: the partial state has one field more than it has columns
Arrow error: Invalid argument error: number of columns(2) must match number of fields(3) in schema
AggregateFunctionExpr::order_bys() returns no expressions for an order-insensitive aggregate, so no ORDER BY columns are fed in. But AggregateFunctionExpr::state_fields() still passes ordering_fields in StateFieldsArgs. Min and Max don't override state_fields, and the default AggregateUDFImpl::state_fields appends ordering_fields, while their accumulators only emit the value.
2. avg, bit_and, bit_or, bit_xor, stddev, var_samp: panic
panicked at datafusion/functions-aggregate/src/average.rs:1101:9:
assertion `left == right` failed: single argument to update_batch
left: 2
right: 1
These don't declare an order_sensitivity, so they get the default HardRequirement. Their ORDER BY expressions are then passed to the accumulator as extra input columns, which their update_batch rejects. The same assertion fires in prim_op.rs:98 (bit_*) and variance.rs:537 (stddev, var_samp). These panic with a single partition too.
To Reproduce
CREATE TABLE d (g INT, k INT, v INT) AS VALUES (1, 2, 20), (1, 1, 10), (2, 4, 40), (2, 3, 30);
SELECT g, min(v ORDER BY k) FROM d GROUP BY g; -- number of columns(2) must match number of fields(3)
SELECT g, avg(v ORDER BY k) FROM d GROUP BY g; -- panics: single argument to update_batch
Grouped, with default settings, on main (e5469e1):
| Aggregate |
Result |
min, max (integer and string) |
schema mismatch |
avg, bit_and, bit_or, bit_xor, stddev, var_samp |
panic |
sum, count, bool_and, bool_or, median, corr, covar_samp, regr_slope, approx_distinct, approx_median |
correct |
first_value, array_agg, string_agg (order-sensitive) |
correct |
A plan that aggregates in a single phase returns the correct result, for example over a single-partition input or with target_partitions = 1. Ungrouped queries are affected too once the input has several partitions, e.g. SELECT min(v ORDER BY k) FROM d WHERE v > 0.
Expected behavior
Each query returns the same result as without the ORDER BY.
Additional context
The existing SUM(amount ORDER BY ts DESC) tests in group_by.slt run as mode=Single, which is likely why this hasn't been caught. I have fixes for both causes and will open them as separate PRs.
Describe the bug
Calling an order-insensitive aggregate with an
ORDER BYfails in a grouped query whenever the plan runs in two phases (Partial→FinalPartitioned), as it does with the defaulttarget_partitions. TheORDER BYshould be ignored, as it already is forsumandcount.There are two failure modes, with different causes.
1.
minandmax: the partial state has one field more than it has columnsAggregateFunctionExpr::order_bys()returns no expressions for an order-insensitive aggregate, so no ORDER BY columns are fed in. ButAggregateFunctionExpr::state_fields()still passesordering_fieldsinStateFieldsArgs.MinandMaxdon't overridestate_fields, and the defaultAggregateUDFImpl::state_fieldsappendsordering_fields, while their accumulators only emit the value.2.
avg,bit_and,bit_or,bit_xor,stddev,var_samp: panicThese don't declare an
order_sensitivity, so they get the defaultHardRequirement. Their ORDER BY expressions are then passed to the accumulator as extra input columns, which theirupdate_batchrejects. The same assertion fires inprim_op.rs:98(bit_*) andvariance.rs:537(stddev,var_samp). These panic with a single partition too.To Reproduce
Grouped, with default settings, on
main(e5469e1):min,max(integer and string)avg,bit_and,bit_or,bit_xor,stddev,var_sampsum,count,bool_and,bool_or,median,corr,covar_samp,regr_slope,approx_distinct,approx_medianfirst_value,array_agg,string_agg(order-sensitive)A plan that aggregates in a single phase returns the correct result, for example over a single-partition input or with
target_partitions = 1. Ungrouped queries are affected too once the input has several partitions, e.g.SELECT min(v ORDER BY k) FROM d WHERE v > 0.Expected behavior
Each query returns the same result as without the
ORDER BY.Additional context
The existing
SUM(amount ORDER BY ts DESC)tests ingroup_by.sltrun asmode=Single, which is likely why this hasn't been caught. I have fixes for both causes and will open them as separate PRs.