[Bug] Incorrect result of COUNT(DISTINCT expr) FILTER (WHERE condition) in Cloudberry 2.1.0

Inizia riproducendo l’esempio SQL con gp_enable_multiphase_agg abilitato e disabilitato, quindi traccia la gestione degli aggregati multiphase per le espressioni DISTINCT con clausole FILTER. Il lavoro è completato quando entrambe le espressioni semanticamente equivalenti restituiscono lo stesso valore in entrambe le modalità, con una copertura di regressione per il caso segnalato.

Issue Body

Describe the bug

Apache Cloudberry returns incorrect results for COUNT(DISTINCT ...) FILTER (WHERE ...)
when gp_enable_multiphase_agg is enabled.

Two semantically equivalent aggregate expressions return different results.

Example:

COUNT(DISTINCT goods_gid)
    FILTER (WHERE invqty = 0)


COUNT(DISTINCT CASE WHEN invqty = 0 THEN goods_gid END)

According to SQL semantics, these two expressions should always return the same value.

However, with gp_enable_multiphase_agg=on, Cloudberry returns different results.

To Reproduce

Create test table:

CREATE TABLE test_cnt
(
    goods_gid int,
    invqty int
)
DISTRIBUTED BY (goods_gid);

Insert test data:

INSERT INTO test_cnt VALUES
(1,0),
(1,1),
(2,0),
(3,1);

Run query with multiphase aggregation enabled:

SET gp_enable_multiphase_agg=on;


SELECT
    COUNT(DISTINCT goods_gid)
        FILTER (WHERE invqty = 0) AS cnt1,


    COUNT(DISTINCT CASE
        WHEN invqty = 0 THEN goods_gid
    END) AS cnt2
FROM test_cnt;

query plan :

Aggregate  (cost=1.12..1.13 rows=1 width=16) (actual time=1.000..1.000 rows=1 loops=1)
  ->  Gather Motion 8:1  (slice1; segments: 8)  (cost=0.00..1.07 rows=5 width=8) (actual time=0.000..0.000 rows=5 loops=1)
        ->  Seq Scan on test_cnt  (cost=0.00..1.01 rows=1 width=8) (actual time=0.000..0.000 rows=2 loops=1)
Planning Time: 1.111 ms
  (slice0)    Executor memory: 31K bytes.
  (slice1)    Executor memory: 39K bytes avg x 8x(0) workers, 112K bytes max (seg0).
Memory used:  128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.177 ms

Actual result:

cnt1 | cnt2
-----+-----
3 | 2

This result is incorrect.

Disable multiphase aggregation:

SET gp_enable_multiphase_agg=off;


SELECT
    COUNT(DISTINCT goods_gid)
        FILTER (WHERE invqty = 0) AS cnt1,


    COUNT(DISTINCT CASE
        WHEN invqty = 0 THEN goods_gid
    END) AS cnt2
FROM test_cnt;

Result:

cnt1 | cnt2
-----+-----
2 | 2

The result is correct.

Expected behavior

The following expressions should always return identical results:

COUNT(DISTINCT expression)
FILTER (WHERE condition)

and

COUNT(DISTINCT CASE WHEN condition THEN expression END)

The value should not depend on whether multiphase aggregation is enabled.

Actual behavior

When:

gp_enable_multiphase_agg=on

Cloudberry returns incorrect aggregate results.

When:

gp_enable_multiphase_agg=off

the result is correct.

Additional information

The issue is reproducible in our production environment.

The problem appears related to the combination of:

COUNT(DISTINCT ...)
FILTER clause
Multiphase aggregation
MPP aggregate execution

The workaround is to rewrite:

COUNT(DISTINCT col)
FILTER (WHERE condition)

as:

COUNT(DISTINCT CASE WHEN condition THEN col END)

or disable:

SET gp_enable_multiphase_agg=off;