cockroachdb / cockroachdb/cockroach

Wrong result from COUNT(DISTINCT ..) FILTER (..) aggregate function

Open
#158,770 3 comments 0 reactions 1 assignee Claimed by @ZhouXing19 View on GitHub
C-bug O-community T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Describe the problem**

When using a `COUNT(DISTINCT ..) FILTER (..)` aggregate function twice, with JDBC bind value(s) involved in the `FILTER` clause, the result is wrong

**To Reproduce**

```java
try (PreparedStatement p = connection.prepareStatement("""
select
count(distinct a.z) filter (where b.x > 1),
count(distinct a.z) filter (where b.x > ?)
from (values (1, 1), (2, 1), (3, 2), (4, 2)) as b (x, y)
join (values (1), (2)) as a (z)
on a.z = b.y
"""
)) {
p.setInt(1, 1);

try (ResultSet rs = p.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getInt(1));
System.out.println(rs.getInt(2));
}
}
}
```

This prints:

```
1
1
```

**Expected behavior**

It should print:

```
2
2
```

The correct result can be achieved when omitting bind values:

```java
try (PreparedStatement p = connection.prepareStatement("""
select
count(distinct a.z) filter (where b.x > 1),
count(distinct a.z) filter (where b.x > 1)
from (values (1, 1), (2, 1), (3, 2), (4, 2)) as b (x, y)
join (values (1), (2)) as a (z)
on a.z = b.y
"""
)) {
try (ResultSet rs = p.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getInt(1));
System.out.println(rs.getInt(2));
}
}
}
```

**Environment:**
- CockroachDB version: CockroachDB CCL v25.4.1 (x86_64-pc-linux-gnu, built 2025/11/26 12:08:42, go1.23.12 X:nocoverageredesign)
- Client app: JDBC

Jira issue: CRDB-57486

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.