new inner join cannot handle more than one one-to-many matches

[colearendt]

Environment

• PostgreSQL version: postgres:12.1
• PostgREST version: PostgREST 8.0.0.20210921 (pre-release) / create a REST API to an existing / Postgres database
• Operating system:

Description of issue

Using the new feature in #1075 - If you have a one-to-many join - lets say client -> contact

client?select=*,contact!inner(*)

Then the query will throw response code 400 - Bad Request with response body:

{
    "hint": null,
    "details": null,
    "code": "42803",
    "message": "aggregate functions are not allowed in FROM clause of their own query level"
}

I will note that I have not tested this example in particular (yet) - I have a much more complex example that is throwing this error. I would assume that the same might occur for an inner join with a many-to-one match as well. (i.e. contact?select=*,client!inner(*))

[steve-chavez]

@colearendt Can you share a sample DDL for this case?

(I made sure to test one-to-many embeds on #1949, there could be something else going on)

[colearendt]

Ahh nice! Apologies for continually missing test cases haha. You're right - what is required to reprex is an inner join on a one-to-one table and an inner join on a one-to-many table.

DDL:

CREATE TABLE client (id serial primary key, name text);
CREATE TABLE contact (id serial primary key, name text, clientid int REFERENCES client(id));
CREATE TABLE clientinfo (id serial primary key, clientid integer unique references client(id), other text);

GRANT SELECT ON TABLE contact TO PUBLIC;
GRANT SELECT ON TABLE client TO public;
GRANT SELECT ON clientinfo TO PUBLIC;

INSERT INTO client (name) values ('Walmart'),('Target'),('Big Lots');
INSERT INTO contact (name, clientid) values ('A',1),('B',1),('C',2),('D',3),('E',3),('F',3) returning *;
INSERT INTO clientinfo (clientid, other) values (1,'something'),(2,'else'),(3,'here');

And then the query that fails:

/client?select=*,contact!inner(*),clientinfo!inner(*)