v10: Wrong query on M2M relationship when using RPC

[steve-chavez]

CREATE TABLE test.groups (
    name text PRIMARY KEY
);

CREATE TABLE test.yards (
    id bigint PRIMARY KEY
);

CREATE TABLE test.group_yard (
    id bigint NOT NULL,
    group_id text NOT NULL REFERENCES test.groups(name),
    yard_id bigint NOT NULL REFERENCES test.yards(id),
    PRIMARY KEY (id, group_id, yard_id)
);

CREATE FUNCTION test.get_yards() RETURNS SETOF test.yards
LANGUAGE sql
AS $$
  select * from test.yards;
$$;

$ curl "localhost:3000/rpc/get_yards?select=groups(*)"

{"code":"42703","details":null,"hint":null,"message":"column get_yards.name does not exist"}

# this works
$ curl "localhost:3000/rpc/get_yards?select=*,groups(*)"
[]

Doesn't happen on v9.0.0 or v9.0.1.

[wolfgangwalther]

I bisected... and no surprise, it's d271942 again ;)

The bad query looks like this:

with pgrst_source as (
select
    "get_yards"."id",
    "get_yards"."name"
from
    "test"."get_yards"()
)
select
    null::bigint as total_result_set,
    pg_catalog.count(_postgrest_t) as page_total,
    coalesce(json_agg(_postgrest_t), '[]')::character varying as body,
    nullif(current_setting('response.headers', true), '') as response_headers,
    nullif(current_setting('response.status', true), '') as response_status
from
    (
    select
        coalesce (
    (
        select
            json_agg("pgrst_source" .*)
        from
            (
            select
                "test"."groups" .*
                from "test"."groups",
                "test"."group_yard"
            where
                "pgrst_source"."id" = "test"."group_yard"."yard_id"
                and "test"."groups"."name" = "test"."group_yard"."group_id" ) "pgrst_source" ),
        '[]') as "groups"
        from "pgrst_source" ) _postgrest_t

while the query with select=*,... looks like this:

with pgrst_source as (
select
    "get_yards" .*
from
    "test"."get_yards"())
select
    null::bigint as total_result_set,
    pg_catalog.count(_postgrest_t) as page_total,
    coalesce(json_agg(_postgrest_t), '[]')::character varying as body,
    nullif(current_setting('response.headers', true), '') as response_headers,
    nullif(current_setting('response.status', true), '') as response_status
from
    (
    select
        "yards" .*,
        coalesce( "yards_groups_1"."yards_groups_1", '[]') as "groups"
    from
        "pgrst_source" as "yards"
    left join lateral (
        select
            json_agg("yards_groups_1") as "yards_groups_1"
            from (
            select
                "test"."groups" .*
            from
                "test"."groups",
                "test"."group_yard"
            where
                "test"."group_yard"."group_id" = "test"."groups"."name"
                and "test"."group_yard"."yard_id" = "yards"."id" ) as "yards_groups_1" ) as "yards_groups_1" on
        true ) _postgrest_t