Unexpected LEFT JOIN generated for a simple query

[rogerrohrbach]

Given this schema + data:

CREATE TABLE fathers (
    id integer PRIMARY KEY,
    name text
);

CREATE TABLE sons (
    id integer PRIMARY KEY,
    father_id integer REFERENCES fathers(id),
    name text
);

insert into fathers values (1, 'Quackmore');
insert into fathers values (2, 'Frederick');
insert into sons values (1, 1, 'Donald');
insert into sons values (2, 2, 'Donald');

this request:

/sons?select=id,fathers(name)&fathers.name=eq.Quackmore&name=eq.Donald

returns

[
  {
    "id": 1,
    "fathers": {
      "name": "Quackmore"
    }
  },
  {
    "id": 2,
    "fathers": null
  }
]

It should return

[
  {
    "id": 1,
    "fathers": {
      "name": "Quackmore"
    }
  }
]

An inspection of the generated SQL

SELECT
  "public"."sons"."id",
  row_to_json("sons_fathers_1".*) AS "fathers"
FROM "public"."sons" LEFT JOIN LATERAL (
  SELECT "fathers_1"."name"
  FROM "public"."fathers" AS "fathers_1"
  WHERE  "fathers_1"."name" = $1
  AND  "fathers_1"."id" = $2
  AND "fathers_1"."id" = "public"."sons"."father_id"
) AS "sons_fathers_1"
ON TRUE WHERE  "public"."sons"."ct_id" = $3

reveals an unexpected LEFT JOIN. Were this INNER JOIN, the correct results would be obtained.

Questions:

1. Is there another way to formulate the request to obtain the correct results?
2. Is this a bug?
3. What is the purpose of AND "fathers_1"."id" = $2? The actual request only has two parameters, so this is being synthesized by the query builder. It seems quite useless.

I'm most urgently looking for an answer to (1); I expect I may need to open an issue to get the other two questions answered.

Any insights appreciated. Thanks.

[wolfgangwalther]

Is there another way to formulate the request to obtain the correct results?

You are looking for top-level filtering via embedding:

https://postgrest.org/en/stable/references/api/resource_embedding.html#top-level-filtering

Two ways to do it: !inner or fathers=not.is.null

Is this a bug?

This is not a bug and works exactly as designed.

What is the purpose of AND "fathers_1"."id" = $2? The actual request only has two parameters, so this is being synthesized by the query builder. It seems quite useless.

This is unexpected. Are you sure the request + query match and you didn't try something else? Can you reproduce that reliably? What does the PostgreSQL log say about which values were passed in for the parametrized query, which value was passed in for $2?

[rogerrohrbach]

@wolfgangwalther Thanks for the speedy reply! I'd not sufficiently RTFM.

I'll be on holiday until next week, but will follow up on your response to my third question when I'm back at the console.