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

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.