Issue: 'content-range' from response header with aggregation function

[jeffoo-k]

Environment

• PostgreSQL version: v14.12
• PostgREST version: v12.2.3
• Operating system: MacOS

Description of issue

Hi there, I'd like to report some buggy 'content-range' value in the response header with aggregation function.

• Minimal SQL definition

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER,
    first_name VARCHAR,
    last_name VARCHAR,
    gender VARCHAR,
    status VARCHAR
);

• How I make a request to Postgrest:
  http:<postgrest_url>/employees?limit=100&offset=0&and=(or(status.like.active))&select=count(),gender
• The behaviour I expect:
  In the response header, "content-range" value should be '0-2/3'. There are only 3 gender values in DB: male, female, undisclosed.
• The actual behaviour:
  In the response header, "content-range" value is '0-2/147'.
  I suspect the total number of record "147" is from the total number of records without aggregation.
  When I make a request without aggregation; http:<postgrest_url>/employees?limit=100&offset=0&and=(or(status.like.active)) I have '0-99/147' as "content-range" value in the response header.

Thank you in advance!

[scott-8]

Also seeing this issue, where "content-range" displays the total number of rows in the table rather than the number of rows after the aggregation.

For example, making this query to a table that has 100 rows with 5 unique values of name:

http://localhost:3000/table_name?selet=count(),name

returns 0-5/100 as content-range, while it should be returning 5.

[taimoorzaeem]

Seems to me that this issue comes from here:

postgrest/src/PostgREST/Query/QueryBuilder.hs

Lines 233 to 245 in 330df05

	readPlanToCountQuery :: ReadPlanTree -> SQL.Snippet
	readPlanToCountQuery (Node ReadPlan{from=mainQi, fromAlias=tblAlias, where_=logicForest, relToParent=rel, relJoinConds} forest) =
	"SELECT 1 " <> fromFrag <>
	(if null logicForest && null relJoinConds && null subQueries
	then mempty
	else " WHERE " ) <>
	intercalateSnippet " AND " (
	map (pgFmtLogicTreeCount qi) logicForest ++
	map pgFmtJoinCondition relJoinConds ++
	subQueries
	)
	where
	qi = getQualifiedIdentifier rel mainQi tblAlias

We don't take into account the aggregates here, hence the wrong Content-Range?

[steve-chavez]

@taimoorzaeem Yes, you're right.

I still don't know if aggregates on select should filter rows automatically, hence change Content-Range.
I've argued that they shouldn't filter rows before #2066 (comment)

There's also some discussion about requiring an explicit group_by on #4002.

So maybe not worth to fix this yet, since the design of aggregates is still not finished.