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?
Code of Conduct
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 noGROUP BY, so it produces exactly one row per outer row, andcount(*) over ()inside it must be1. GPORCA decorrelates the subquery into aGroupAggregategrouped by the correlation column and puts theWindowAggon 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 forWHERE).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
With
optimizer = off, the target-list query returns the expected4 | 1 | 3. TheWHEREquery 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:SET optimizer = off;fixes the target-list form. On REL_2_STABLE it does not fix theWHEREform.Operating System
any
Anything else
No response
Are you willing to submit PR?
Code of Conduct