hasura / hasura/graphql-engine
Count aggregate that takes multiple columns does not compute correct results
- Dominant language
- TypeScript
- Stars
- 32.1k
- Forks
- 3k
- PR merge metrics
- PR metrics pending
Description
### Version Information
Server Version: 2.8.4
### Environment
OSS
### What is the current behaviour?
For a Postgres data source, if you issue a GraphQL aggregate query using a count aggregation function that takes multiple columns, such as:
```graphql
query {
Invoice_aggregate {
aggregate {
count(columns: [BillingState, BillingPostalCode])
}
}
}
```
this returns a count of all rows in the Invoice table. This is incorrect.
### What is the expected behaviour?
The expected behaviour is to return a count of all rows where either/both `BillingState` and `BillingPostalCode` are not null. (ie. if both are null, then the row is not counted).
### Please provide any traces or logs that could help here.
The SQL generated for the above query is:
```sql
SELECT
json_build_object(
'aggregate',
json_build_object(
'count',
COUNT(("BillingState", "BillingPostalCode"))
)
) AS "root"
FROM
(
SELECT
"_0_root.base"."BillingState" AS "BillingState",
"_0_root.base"."BillingPostalCode" AS "BillingPostalCode"
FROM
(
SELECT
*
FROM
"public"."Invoice"
WHERE
('true')
) AS "_0_root.base"
) AS "_1_root"
```
The problem is that COUNT is being passed a tuple of BillingState and BillingPostalCode, and this tuple is never null (regardless of if the columns themselves are). So all rows are counted.
### Any possible solutions?
Instead of tupling the columns, pass them to the `COALESCE` function. However, this would produce rather incoherent behaviour when `distinct` is also set to true on the count field.
I think the best "solution" would simply be removing the ability to count across multiple columns. It is not supported by the SQL Server backend already, and is broken in Postgres right now. If you want to count rows that are not null across multiple columns, use a filter in the GraphQL and a plain count.
Contributor guide
Research direction
Start by reproducing the Invoice_aggregate count query against a Postgres data source and inspect the generated SQL shown in the report. Trace how GraphQL count aggregations with multiple columns are handled, comparing the Postgres behavior with the stated SQL Server limitation. Done means the multi-column count behavior is consistent with the documented expected semantics or the unsupported operation is removed.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- graphql, postgres, typescript
- Domain
- api, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100