apache / apache/cloudberry

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

Open
#1,916 0 comments 4 reactions 0 assignees View on GitHub
type: Bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.