jayzhan211 opened a new issue, #27220: URL: https://github.com/apache/datafusion/issues/27220
### Describe the bug An aggregate with a `FILTER` clause and no `GROUP BY`, over a subquery that is itself an aggregate with no `GROUP BY`, fails planning with an internal error. The physical optimizer rule `AggregateStatistics` matches the subquery's aggregate instead of the outer aggregate's own partial stage. ### To Reproduce In `datafusion-cli`: ```sql CREATE TABLE t AS VALUES (1), (2), (3), (4), (5); -- Query 1 SELECT count(*) FILTER (WHERE c > 0) AS n FROM (SELECT count(*) AS c FROM t); -- Query 2 SELECT count(*) AS n FROM (SELECT count(*) AS c FROM t); ``` ### Expected behavior | Query | DataFusion returns | Expected | |---|---|---| | 1 | `aggregate_statistics caused by Internal error: Assertion failed: col.name() == matching_name: Input field name count(Int64(1)) does not match with the projection expression count(Int64(1)) FILTER (WHERE c > Int64(0)).` | 1 | | 2 | 1 | 1 | ### Additional context - Cause: for a `Final` aggregate without `GROUP BY`, `take_optimizable` (`datafusion/physical-optimizer/src/aggregate_statistics.rs:135-151`) walks down single-child nodes until it finds a raw-input aggregate with no `GROUP BY` and no filter. The outer aggregate's own `Partial` fails the no-filter check (`:143`), so the walk does not stop there. It continues into the subquery and returns the subquery's `Partial`. The rule then replaces the outer `Final` with values computed from the subquery's aggregate and statistics. The output name check catches the mismatch, so the result is an error rather than wrong rows. - The walk has had this shape since at least 41.0.0 (it moved files in #11798). - Fix direction: stop at the first aggregate the walk meets, and only use it if it is this `Final`'s own `Partial`. ### Willingness to contribute I'm not planning to fix this myself, so anyone is welcome to pick it up. Feel free to ping me with questions about the cause or when you'd like a review. -- 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]
