cockroachdb / cockroachdb/cockroach
Wrong result from COUNT(DISTINCT ..) FILTER (..) aggregate function
- 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
Assessment
This issue has not been assessed yet.