adriangb opened a new issue, #19410:
URL: https://github.com/apache/datafusion/issues/19410
### Describe the bug
In `main` running this query fails with
```
Error during planning: Column in ORDER BY must be in GROUP BY or an
aggregate function: While expanding wildcard, column "Count" must appear in the
GROUP BY clause or must be part of an aggregate function, currently only "t.x,
count(Int64(1)), count" appears in the SELECT clause satisfies this requirement
```
### To Reproduce
```sql
> with t as (select 'foo' as user_agent)
select user_agent "User Agent", count(*) as "Count"
from t
where user_agent not like 'Mozilla%'
group by "User Agent"
order by 2 desc;
Error during planning: Column in ORDER BY must be in GROUP BY or an
aggregate function: While expanding wildcard, column "Count" must appear in the
GROUP BY clause or must be part of an aggregate function, currently only
"t.user_agent, count(Int64(1)), User Agent, count" appears in the SELECT clause
satisfies this requirement
````
### Expected behavior
Query succeeds.
In v50:
```
❯ datafusion-cli
DataFusion CLI v50.0.0
> with t as (select 'foo' as x)
select x, count(*) as "Count"
from t
group by x
order by 2 desc;
+-----+-------+
| x | Count |
+-----+-------+
| foo | 1 |
+-----+-------+
1 row(s) fetched.
Elapsed 0.003 seconds.
```
In DuckDB:
```
❯ duckdb
DuckDB v1.3.2 (Ossivalis) 0b83e5d2f6
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D with t as (select 'foo' as x)
select x, count(*) as "Count"
from t
group by x
order by 2 desc;
┌─────────┬───────┐
│ x │ Count │
│ varchar │ int64 │
├─────────┼───────┤
│ foo │ 1 │
└─────────┴───────┘
```
### Additional context
_No response_
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
