Aggregation can't handle Empty Embed

[mertcorumlu]

Environment

• PostgreSQL version: 17.6.1
• PostgREST version: 13.0.7
• Operating system: Macos 15.6.1

Description of issue

I'm trying to count items in each group, but also want to filter by a nested embedding which is not a part of the group by or select.

There is something similar which allows filtering by an embed without actually selecting them using Empty Embed, this does not work once I add an aggregate function.

Altough this works just fine:
http://localhost:8080/select=group_column, id.count()

For this one I'm getting error:
http://localhost:8080/select=group_column,child(nested_child()),id.count()&child.nested_child.id=eq.1

{
    "code": "42703",
    "details": null,
    "hint": null,
    "message": "column \"child\" does not exist"
}

But without grouping this one works fine again:
http://localhost:8080/select=group_column,child(nested_child())&child.nested_child().id=eq.1

One solution that one can do is of course:
http://localhost:8080/select=group_column,child(nested_child(id)),id.count()&child.nested_child.id=eq.1

but this adds a new group by dimension child.nested_child.id, which I don't want to have.

I think the expected behavior here with the definition of Empty Embed is that PostgREST should just join the Embeds for filtering but ignore them in select and group by clauses, which it fails to do right now.

[laurenceisla]

I think the expected behavior here with the definition of Empty Embed is that PostgREST should just join the Embeds for filtering but ignore them in select and group by clauses, which it fails to do right now.

Agree. So for example in this request from our spec tests:

curl --get 'localhost:3000/processes' \
  -d 'select=category_id,count(),supervisors()' \
  -d 'supervisors.id=neq.1' \
  -d 'supervisors=not.is.null'

{
  "code": "42703",
  "details": null,
  "hint": null,
  "message": "column \"supervisors\" does not exist"
}

The generated query would be this one (see the commented line):

WITH pgrst_source AS
  (SELECT "test"."processes"."category_id",
          COUNT("test"."processes".*)
   FROM "test"."processes"
   LEFT JOIN LATERAL
     (SELECT json_agg("processes_supervisors_1")::jsonb AS "processes_supervisors_1"
      FROM
        (SELECT "test"."supervisors".*
         FROM "test"."supervisors",
              "test"."process_supervisor"
         WHERE "test"."supervisors"."id" <> 1
           AND "test"."process_supervisor"."supervisor_id" = "test"."supervisors"."id"
           AND "test"."process_supervisor"."process_id" = "test"."processes"."id") AS "processes_supervisors_1") AS "processes_supervisors_1" ON TRUE
   WHERE "processes_supervisors_1" IS DISTINCT
     FROM NULL
   GROUP BY "test"."processes"."category_id",
            -- Here's the culprit. We'd need to remove "supervisors" since it's not involved in the count (in the top SELECT)
            "supervisors")
SELECT NULL::bigint AS total_result_set,
       pg_catalog.count(_postgrest_t) AS page_total,
       coalesce(json_agg(_postgrest_t), '[]') AS body,
       nullif(current_setting('response.headers', TRUE), '') AS response_headers,
       nullif(current_setting('response.status', TRUE), '') AS response_status,
       '' AS response_inserted
FROM
  (SELECT *
   FROM pgrst_source) _postgrest_t;

Note that in my example I added supervisors=not.is.null. IMO, this is needed to filter the top resource processes with the values of the nested one supervisors, otherwise those processes without supervisors would be included in the count aggregate (unless there's a use case that needs this that I'm not aware of).
Edit: I guess "count all processes even if they don't have supervisors but don't belong to supervisor X" is a valid use case.