Counting distinct values using filter clauses is distinct across filter statements

**Describe the problem**

Please describe the issue you observed, and any steps we can take to reproduce it:

We have common table expression that returns a table with two columns. The first column represents an ID, the second represents a enum with a known set of values. The final query using that CTE (same behavior with a sub query though) needs to figure out how many unique IDs are in each enum value. It is expected for one ID to be counted multiple times if there are multiple rows with the same ID and different value.

To narrow down the issue, we've just tried it with a static query that includes data for easier debugging.

**To Reproduce**

What did you do? Describe in your own words.

If possible, provide steps to reproduce the behavior:

1. Start cockroachdb in insecure single node mode. We've used this container image https://hub.docker.com/layers/cockroachdb/cockroach/v24.1.3/images/sha256-d0378e606ce0536be7bf2d946f026562e025837169d20eb2880e5c1b813ac975?context=explore
2. Run the following query
```sql
with data as (
select
*
from unnest(
ARRAY[1, 2, 3, 4, 1],
ARRAY[true, true, true, true, false]
) as t(id, value)
)
select
count(distinct id),
count(distinct id) filter (where value is true),
count(distinct id) filter (where value is false)
from data
```
3. Query returns 4, 4, 0
4. Execute the same query, however exclude the two first counts from the select. The query should look like this:
```sql
with data as (
select
*
from unnest(
ARRAY[1, 2, 3, 4, 1],
ARRAY[true, true, true, true, false]
) as t(id, value)
)
select
count(distinct id) filter (where value is false)
from data
```
5. Query returns 1

**Expected behavior**
I expect the query in step 3 to return 4, 4, 1
Right now it looks like a distinct count with a filter clause does not consider `distinct` per filter clause but across all filter clauses in the query.

**Additional data / screenshots**
No additional data.

**Environment:**
- CockroachDB version [e.g. 2.0.x]: 24.1.3
- Server OS: [e.g. Linux/Distrib]: linux/amd64
- Client app [e.g. `cockroach sql`, JDBC, ...]: `cockroach sql`

**Additional context**
I'm not sure if this is a bug in CockroachDB or a quirk of the SQL specification and it is indeed functioning correctly. It seems unintuitive in either case though.

Jira issue: CRDB-42366