cblp opened a new issue, #26221:
URL: https://github.com/apache/datafusion/issues/26221
### Describe the bug
A query that aggregates both a column and a cast of the same column fails to
plan:
```
Schema error: Schema contains duplicate unqualified field name "sum(t.x)"
```
Aliases on the aggregates do not help. It happens with any aggregate
function and any target type, e.g. `sum(x)` and `sum(CAST(x AS FLOAT))`, or
`count(x)` and `count(CAST(x AS BIGINT))`.
The schema name of `CAST(expr AS type)` is the schema name of `expr`
(`Expr::Cast` and `Expr::TryCast` in `SchemaDisplay`,
`datafusion/expr/src/expr.rs`, introduced in #3222 for consistency with
Postgres and Spark). So `sum(CAST(x AS FLOAT))` gets the same name `sum(t.x)`
as `sum(x)` in the `Aggregate` node, before the user's aliases are applied in
the projection above it.
### To Reproduce
In `datafusion-cli`:
```sql
SELECT sum(x) AS a, sum(CAST(x AS FLOAT)) AS b FROM (VALUES (1.0), (2.0)) AS
t(x);
-- Schema error: Schema contains duplicate unqualified field name "sum(t.x)"
SELECT sum(x), sum(CAST(x AS FLOAT)) FROM (VALUES (1.0), (2.0)) AS t(x);
-- Error during planning: Projections require unique expression names but
the expression "sum(t.x)" at position 0
-- and "sum(CAST(t.x AS Float32))" at position 1 have the same name.
Consider aliasing ("AS") one of them.
SELECT count(x) AS a, count(CAST(x AS BIGINT)) AS b FROM (VALUES (1.0),
(2.0)) AS t(x);
-- Schema error: Schema contains duplicate unqualified field name
"count(t.x)"
```
The second error message suggests aliasing, but aliasing leads to the first
error.
Computing the cast in a subquery works:
```sql
SELECT sum(x) AS a, sum(y) AS b
FROM (SELECT x, CAST(x AS FLOAT) AS y FROM (VALUES (1.0), (2.0)) AS t(x));
-- 3.0 | 3.0
```
### Expected behavior
Both aggregates are computed, since they are different expressions: `3.0`
and `3.0` in the first query.
### Additional context
Reproduced on `main` at commit 6663e4348 (debug build of `datafusion-cli`).
### Willingness to contribute
None
--
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]
