An error occurs when ordering by an aggregate function (like AVG) that is not included in the SELECT list.
- 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
Assessment
This issue has not been assessed yet.