cetra3 opened a new issue, #27287:
URL: https://github.com/apache/datafusion/issues/27287
_Posted by Claude (an AI assistant) on behalf of cetra3._
### Describe the bug
With `datafusion.execution.parquet.pushdown_filters = true`, a query that
groups by a `CASE` expression and pins the group key with `HAVING` fails to
plan with `SanityCheckPlan`. The `CASE` repeats a subexpression, so common
subexpression elimination adds a `ProjectionExec` above the scan.
The `HAVING` filter is pushed into the parquet predicate, which makes the
group key constant. The partial `AggregateExec` relies on that and requires its
input ordered by the key. But the `ProjectionExec` between the scan and the
aggregate rewrites `a = 'x'` to `__common_expr_1`, and the constant does not
survive that projection, so the final plan check rejects the plan.
This plans and returns the right row on 55.2.0.
### To Reproduce
`datafusion-cli` at `main` (c8265b6872, 2026-11-10):
```sql
SET datafusion.execution.parquet.pushdown_filters = true;
COPY (SELECT * FROM (VALUES ('x', 'y'), ('x', 'z'), ('w', 'y')) AS v(a, b))
TO 't.parquet';
CREATE EXTERNAL TABLE t STORED AS PARQUET LOCATION 't.parquet';
SELECT CASE WHEN a = 'x' AND b = 'y' THEN b WHEN a = 'x' THEN 'z' END AS e,
count(*)
FROM t
GROUP BY e
HAVING e = 'y';
```
```
SanityCheckPlan: the plan produced by physical optimizer rule
'EnsureRequirements' does not satisfy the order and distribution requirements
caused by
Error during planning: Plan: ["AggregateExec: mode=Partial, gby=[CASE WHEN
__common_expr_1@0 AND b@1 = y THEN b@1 WHEN __common_expr_1@0 THEN z END as
CASE WHEN t.a = Utf8(\"x\") AND t.b = Utf8(\"y\") THEN t.b WHEN t.a =
Utf8(\"x\") THEN Utf8(\"z\") END], aggr=[count(Int64(1))]", " ProjectionExec:
expr=[a@0 = x as __common_expr_1, b@1 as b]", " RepartitionExec:
partitioning=RoundRobinBatch(32), input_partitions=1", " DataSourceExec:
file_groups={1 group: [[.../t.parquet]]}, projection=[a, b], file_type=parquet,
predicate=CASE WHEN a@0 = x AND b@1 = y THEN b@1 WHEN a@0 = x THEN z END = y"]
does not satisfy order requirements: [CASE WHEN __common_expr_1@0 AND b@1 = y
THEN b@1 WHEN __common_expr_1@0 THEN z END ASC]. Child-0 order: []
```
### Expected behavior
One row, `e = 'y'`, `count = 1`, as 55.2.0 returns.
### Additional context
- `git bisect` between 55.2.0's merge base with `main` and `main` points at
99e723dd47, "fix: retain only valid orderings after order-preserving
repartition" (#26747). Its parent ce0e8293f9 returns the right row.
- It also fails with `target_partitions = 1`.
- Each of these makes it plan and return the right row:
- `pushdown_filters = false`.
- An in-memory table (`CREATE TABLE t AS VALUES ...`) in place of parquet.
- A `CASE` with no repeated subexpression, so CSE adds no projection, for
example `CASE WHEN a = 'x' THEN b ELSE 'z' END`.
- `WHERE <the same CASE> = 'y' ... ORDER BY e` with no `GROUP BY`.
- This looks related to #26624 (fixed by #27123), where a constant was lost
when equivalence properties were projected through a sort-merge join. That fix
is in `main`, and this query still fails. The open PR #27191 does not fix it
either. I built its head (434a1272bb) and got the same error.
--
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]
