Braedon-Wooding-Displayr opened a new issue, #24042:
URL: https://github.com/apache/datafusion/issues/24042
### Describe the bug
If the same expression is written in both the SELECT list and the GROUP BY,
and it
contains a placeholder, the two do not compare equal in the logical plan. The
grouping key is therefore not recognised as covering the SELECT expression,
and any
column inside it is reported as ungrouped. Replacing the placeholder with a
literal
makes the identical query plan fine.
The cause is that the two clauses are planned by different entry points.
`sql_to_expr`, used for the SELECT list, ends with a call to
`Expr::infer_placeholder_types`. The GROUP BY clause is planned by
`sql_expr_to_logical_expr` (`sql/src/select.rs`), which does not. So the
projection
holds `Placeholder { field: Some(..) }` and the grouping key holds
`Placeholder { field: None }`, and `Expr` equality fails on the `field`.
HAVING, QUALIFY (both in `select.rs`) and ORDER BY
(`sql/src/expr/order_by.rs`) are
planned the same way and have the same problem. DISTINCT ON and DISTRIBUTE
BY also
skip inference, though nothing compares those against the SELECT list.
`PREPARE` is unaffected when the parameter type is declared, since the type
is then
known at parse time in both positions.
### To Reproduce
Against a table `t(x bigint)`:
```sql
-- fails to plan
SELECT CASE WHEN x < $1 THEN 'low' ELSE 'high' END, count(*)
FROM t
GROUP BY CASE WHEN x < $1 THEN 'low' ELSE 'high' END;
```
```
Error during planning: Column in SELECT must be in GROUP BY or an aggregate
function:
While expanding wildcard, column "t.x" must appear in the GROUP BY clause or
must be
part of an aggregate function, currently only "CASE WHEN t.x < $1 THEN
Utf8("low")
ELSE Utf8("high") END, count(Int64(1))" appears in the SELECT clause
satisfies this
requirement
```
Note that the error lists the CASE as satisfying the requirement while still
rejecting the column inside it.
Both of these plan without complaint:
```sql
-- literal instead of the placeholder
SELECT CASE WHEN x < 3 THEN 'low' ELSE 'high' END, count(*)
FROM t
GROUP BY CASE WHEN x < 3 THEN 'low' ELSE 'high' END;
-- placeholder, but with a declared type
PREPARE p(BIGINT) AS
SELECT CASE WHEN x < $1 THEN 'low' ELSE 'high' END, count(*)
FROM t
GROUP BY CASE WHEN x < $1 THEN 'low' ELSE 'high' END;
```
Reproduced on main.
### Expected behavior
The placeholder version should plan the same as the literal version.
Identical text in the SELECT list and the GROUP BY should produce one
expression, so the CASE covers `x` and no further grouping is required.
Calling `infer_placeholder_types` on the planned GROUP BY, HAVING, QUALIFY
and ORDER BY expressions, as `sql_to_expr` already does for the SELECT list, is
enough to fix it.
### Additional context
Somewhat vaguely similar to #19321 though that's related to typing and this
is just a parsing bug.
--
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]
