[Bug] Wrong results: GPORCA evaluates a window function in a correlated aggregate subquery over all groups

The issue is in GPORCA's decorrelation logic for subqueries containing window functions. Start by examining the query planner code that transforms correlated subqueries, particularly around GroupAggregate and WindowAgg nodes. Look at the test case in the issue to understand the wrong grouping. The fix likely involves ensuring the window function is evaluated after aggregation, not across groups. Run the provided SQL to reproduce the bug and verify with optimizer on/off.

Apache Cloudberry version

main; REL_2_STABLE

What happened

With GPORCA (optimizer = on), a correlated scalar subquery that combines an aggregate with a window function returns a wrong value. The subquery has no GROUP BY, so it produces exactly one row per outer row, and count(*) over () inside it must be 1. GPORCA decorrelates the subquery into a GroupAggregate grouped by the correlation column and puts the WindowAgg on top. The window then runs over all groups instead of the single aggregate row.

This gives a wrong value in the target list and wrong rows in WHERE. The Postgres planner (optimizer = off) computes the target-list form correctly (see below for WHERE).

What you think should happen instead

count(*) over () inside the subquery should see only the single result row of the ungrouped aggregate, as it does with the Postgres planner.

How to reproduce

create table w1(a int, b int); insert into w1 values (4,1);
create table w2(a int);        insert into w2 values (1),(1),(2);
analyze w1; analyze w2;

set optimizer = on;

select a, b, (select sum(w2.a) + count(*) over () from w2 where w2.a = w1.b) from w1;
--  a | b | ?column?
-- ---+---+----------
--  4 | 1 |        4      <-- WRONG, expected 3 (sum = 2, count(*) over () = 1)

select a, b from w1 where w1.a > (select sum(w2.a) + count(*) over () from w2 where w2.a = w1.b);
-- (0 rows)                <-- WRONG, expected 4 | 1

With optimizer = off, the target-list query returns the expected 4 | 1 | 3. The WHERE query is correct on main only when the pull-up is guarded against window functions (with patch from #1933). On REL_2_STABLE, the Postgres planner also returns 0 rows for it.

Plan with optimizer = on:

explain (costs off)
select a, b from w1 where w1.a > (select sum(w2.a) + count(*) over () from w2 where w2.a = w1.b);

 Hash Join
   Join Filter: (w1.a > ((sum(w2.a)) + count(*) OVER (?)))
   ->  WindowAgg
         ->  GroupAggregate
               Group Key: w2.a        <-- window is computed across all groups

SET optimizer = off; fixes the target-list form. On REL_2_STABLE it does not fix the WHERE form.

Operating System

any

Anything else

No response

Are you willing to submit PR?

• Yes, I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct.