REGRESSION: SanityCheckPlan error for GROUP BY ... HAVING on a CASE key with parquet filter pushdown #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 (c8265b6, 2026-11-10):

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 99e723d, "fix: retain only valid orderings after order-preserving repartition" (fix: retain only valid orderings after order-preserving repartition #26747). Its parent ce0e829 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 REGRESSION: SanityCheckPlan error with sort-merge join when a filter makes a sort or join key constant (constant lost in EquivalenceGroup projection) #26624 (fixed by fix: preserve constant equivalence properties through projection #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 fix: SanityCheckPlan error when a per-partition constant leads an ordering #27191 does not fix it either. I built its head (434a127) and got the same error.