[Bug] Wrong results: GPORCA returns NULL instead of the empty-input value for correlated aggregate subqueries other than count

The bug is in GPORCA's handling of correlated aggregate subqueries. Start by examining the optimizer code for scalar subquery transformation, likely in the CTranslatorScalarToDXL or CTranslatorQueryToDXL modules. Look for special-case logic for COUNT and extend it to other aggregates. The test case provided can be run to verify the fix. Check the Postgres planner's behavior as a reference for correct results.

Apache Cloudberry version

main, REL_2_STABLE

What happened

With GPORCA (optimizer = on), a correlated scalar subquery with an aggregate returns NULL for outer rows that have no matching inner rows. That is only correct for aggregates whose value on empty input is NULL. GPORCA special-cases count only, so other aggregates that return a non-NULL value on empty input come out wrong:

• regr_count (empty input → 0)
• hypothetical-set aggregates rank / dense_rank / percent_rank / cume_dist ... WITHIN GROUP (→ 1 / 1 / 0 / 1)
• any user-defined aggregate with a non-NULL initcond

The Postgres planner (optimizer = off) returns the correct values for the same query.

What you think should happen instead

A correlated aggregate subquery with no matching rows should return the aggregate's value on empty input, just as it does when run on its own (... WHERE false) and as the Postgres planner returns it.

How to reproduce

create table t1(a int, b int, d int);
insert into t1 values (3,1,1),(1,9,5),(0,2,7),(5,5,1),(2,4,9);
create table t2(a int, b int);
insert into t2 values (1,10),(2,20),(1,30);
analyze t1; analyze t2;

create aggregate sum_from_zero(int) (sfunc = int4pl, stype = int4, initcond = '0');

-- values on empty input
select regr_count(a,b), rank(5) within group (order by b), sum_from_zero(a)
  from t2 where false;
--  regr_count | rank | sum_from_zero
-- ------------+------+---------------
--           0 |    1 |             0

set optimizer = on;
select a, d,
       (select count(*) from t2 where t2.a = t1.d)                            as cnt,
       (select regr_count(t2.a,t2.b) from t2 where t2.a = t1.d)               as regr,
       (select rank(5) within group (order by t2.b) from t2 where t2.a = t1.d) as rnk,
       (select sum_from_zero(t2.a) from t2 where t2.a = t1.d)                 as sfz
  from t1 order by 1,2;

Result with optimizer = on:

 a | d | cnt | regr | rnk | sfz
---+---+-----+------+-----+-----
 0 | 7 |   0 |      |     |        <-- WRONG, expected 0 | 1 | 0
 1 | 5 |   0 |      |     |        <-- WRONG
 2 | 9 |   0 |      |     |        <-- WRONG
 3 | 1 |   2 |    2 |   1 |   2
 5 | 1 |   2 |    2 |   1 |   2

Result with optimizer = off (correct):

 a | d | cnt | regr | rnk | sfz
---+---+-----+------+-----+-----
 0 | 7 |   0 |    0 |   1 |   0
 1 | 5 |   0 |    0 |   1 |   0
 2 | 9 |   0 |    0 |   1 |   0
 3 | 1 |   2 |    2 |   1 |   2
 5 | 1 |   2 |    2 |   1 |   2

Operating System

any

Anything else

The same subquery in WHERE loses the no-match rows, because it gets turned into an inner join:

select a,b,d from t1 where t1.a > (select regr_count(t2.a,t2.b) from t2 where t2.a = t1.d) order by 1,2,3;
-- returns 2 rows (3|1|1, 5|5|1); expected 4 (also 1|9|5 and 2|4|9)

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.