[Bug] Incorrect result of COUNT(DISTINCT expr) FILTER (WHERE condition) in Cloudberry 2.1.0
- Dominant language
- C
- Stars
- 1.4k
- Forks
- 247
- Avg merge
- 4d 3h
- Merged PRs (30d)
- 39
Description
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:
```sql
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;
Contributor guide
Research direction
Start by reproducing the SQL example with gp_enable_multiphase_agg enabled and disabled, then trace the multiphase aggregate handling for DISTINCT expressions with FILTER clauses. Done means both semantically equivalent expressions return the same value in both modes, with regression coverage for the reported case.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- c, postgresql, sql
- Domain
- databases, distributed-systems
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 65/100