[dbolympics] Subqueries in an aggregate (GROUP BY key, aggregate argument or FILTER) fail with "Physical plan does not support logical expression" #26704

Describe the bug

A subquery (EXISTS, IN or a scalar subquery) inside an aggregate, that is in a GROUP BY key, an aggregate argument or an aggregate FILTER, fails at physical planning with This feature is not implemented: Physical plan does not support logical expression .... The same subquery in the SELECT list or in WHERE works.

To Reproduce

CREATE TABLE t1 (a INT, b VARCHAR, x INT);
INSERT INTO t1 VALUES (1, 'p', 10), (2, 'p', 20), (3, 'q', 30), (NULL, 'q', 40), (2, 'q', 50);
CREATE TABLE t2 (a INT, y INT);
INSERT INTO t2 VALUES (2, 7), (2, 9), (3, 5), (NULL, 1);

-- Query 1
SELECT count(*) FILTER (WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.a = t1.a)) AS n FROM t1;
-- Query 2
SELECT CASE WHEN EXISTS (SELECT 1 FROM t2 WHERE t2.a = t1.a) THEN 'yes' ELSE 'no' END AS has_t2, count(*) AS n
FROM t1 GROUP BY 1 ORDER BY 1;
-- Query 3
SELECT sum(CASE WHEN t1.a IN (SELECT a FROM t2) THEN t1.x ELSE 0 END) AS s FROM t1;
-- Query 4
SELECT t1.b, sum((SELECT max(t2.y) FROM t2 WHERE t2.a = t1.a)) AS s FROM t1 GROUP BY t1.b ORDER BY t1.b;
-- Query 5
SELECT count(*) FILTER (WHERE t1.a NOT IN (SELECT a FROM t2)) AS n FROM t1;

Expected behavior

DuckDB returns the expected results.

Additional context

• The cause: DecorrelatePredicateSubquery and ScalarSubqueryToJoin rewrite subqueries only in Filter and Projection nodes (decorrelate_predicate_subquery.rs matches LogicalPlan::Projection and LogicalPlan::Filter, scalar_subquery_to_join.rs the same two). The expressions of an Aggregate are never visited, so their subqueries reach the physical planner unchanged.
• This shows up in the dbolympics benchmark ([dbolympics] EPIC: Review "db olympics" results #26661): 13 of its 409 queries put a subquery in an aggregate (q057, q084, q085, q091, q135, q136, q156, q178, q182, q312, q315, q370, q409).
• A fix direction that removes the cause: compute each aggregate expression that contains a subquery in a Projection below the Aggregate, keeping the output names with aliases. These expressions are evaluated once per input row anyway, and the existing rules then decorrelate the subquery in that Projection. I tried this in a prototype (one new optimizer rule placed before DecorrelatePredicateSubquery):
  • Queries 1-5 return the expected results, as do harder cases (several subqueries in one aggregate, count(DISTINCT ...), an aggregate ORDER BY, an aggregate inside a correlated subquery, NOT EXISTS, empty input).
  • 10 of the 13 dbolympics queries plan. The other 3 then fail on a scalar subquery with ORDER BY ... LIMIT 1, which is a separate gap.
  • No other dbolympics query starts or stops failing, and all sqllogictest files and optimizer tests pass (the only expected-output change is the new rule's line in EXPLAIN VERBOSE).
  • Like ScalarSubqueryToJoin, it leaves an uncorrelated scalar subquery where it is, since ScalarSubqueryExec already runs it.
• Not covered by that approach: a subquery in a ROLLUP / GROUPING SETS key. It fails differently on main today (Schema error: No field named t1.a).
• Related: [dbolympics] General framework to decorrelate the subqueries #5492 (general decorrelation framework).

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.