apache / apache/datafusion

An error occurs when ordering by an aggregate function (like AVG) that is not included in the SELECT list.

Open
#15,875 1 comment 0 reactions 1 assignee Claimed by @UBarney View on GitHub
bug
Dominant language
Rust
Stars
9.3k
Forks
2.4k
Avg merge
3d 7h
Merged PRs (30d)
344

Description

### Describe the bug

Executing the SQL query `SELECT value, max(value) + min(value) FROM generate_series(1, 5) GROUP BY value ORDER BY avg(value);` results in an error

### To Reproduce

```
> SELECT value, max(value) + min(value) FROM generate_series(1, 5) group by value order by avg(value);
This feature is not implemented: Physical plan does not support logical expression AggregateFunction(AggregateFunction { func: AggregateUDF { inner: Avg { signature: Signature { type_signature: UserDefined, volatility: Immutable }, aliases: ["mean"] } }, params: AggregateFunctionParams { args: [Cast(Cast { expr: Column(Column { relation: Some(Bare { table: "tmp_table" }), name: "value" }), data_type: Float64 })], distinct: false, filter: None, order_by: None, null_treatment: None } })

> explain format indent SELECT value, max(value) + min(value) FROM generate_series(1, 5) group by value order by avg(value);
+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type | plan |
+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| logical_plan | Sort: avg(CAST(tmp_table.value AS Float64)) ASC NULLS LAST |
| | Projection: tmp_table.value, max(tmp_table.value) + min(tmp_table.value) |
| | Aggregate: groupBy=[[tmp_table.value]], aggr=[[max(tmp_table.value), min(tmp_table.value)]] |
| | TableScan: tmp_table projection=[value] |
| physical_plan_error | This feature is not implemented: Physical plan does not support logical expression AggregateFunction(AggregateFunction { func: AggregateUDF { inner: Avg { signature: Signature { type_signature: UserDefined, volatility: Immutable }, aliases: ["mean"] } }, params: AggregateFunctionParams { args: [Cast(Cast { expr: Column(Column { relation: Some(Bare { table: "tmp_table" }), name: "value" }), data_type: Float64 })], distinct: false, filter: None, order_by: None, null_treatment: None } }) |
+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 row(s) fetched.
Elapsed 0.002 seconds.
```

### Expected behavior

return result
```
SELECT value, max(value) + min(value) FROM generate_series(1, 5) group by value order by avg(value);
1 2
2 4
3 6
4 8
5 10
```

### Additional context

_No response_

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.