Embedding of views through composite keys with different column order broken

[wolfgangwalther]

Environment

• PostgreSQL version: 15.0, 14.5
• PostgREST version: 10.0.0.20221011 (pre-release), 10.0.0

Description of issue

Taking the following schema:

create table a (
  primary key (c1, c2),
  c1 int,
  c2 bool
);

create table b (
  c2 bool,
  c1 int,
  foreign key (c1, c2) references a
);

create view v1 as table a;

create view v2 as table b;

and doing the following request:

GET /v1?select=v2(*) HTTP/1.1

throws the following error:

{
  "code":"42883",
  "details":null,
  "hint":"No operator matches the given name and argument types. You might need to add explicit type casts.","message":"operator does not exist: boolean = integer"
}

This is because the query generated for this embedding looks like this:

SELECT COALESCE( "v1_v2"."_v1_v2", '[]') AS "v2"
  FROM "public"."v1"
       LEFT JOIN LATERAL (
         SELECT json_agg("_v1_v2") AS "_v1_v2"
           FROM (SELECT "public"."v2".*
                   FROM "public"."v2"
                  WHERE "public"."v2"."c2" = "public"."v1"."c1"
                        AND "public"."v2"."c1" = "public"."v1"."c2"
                  LIMIT $1
                 OFFSET $2
                ) AS "_v1_v2"
       ) AS "v1_v2" ON TRUE
 LIMIT $3
OFFSET $4

The WHERE for that join is just flat out wrong - it compares c1 and c2 with each other?

[wolfgangwalther]

Note, that this bug only surfaced for me, because the column types were not matching. If the column types match, this will silently succeed, but might return wrong results.

[wolfgangwalther]

I was not able to reproduce this with plain tables only, i.e. without views.

[steve-chavez]

@wolfgangwalther Is this one a regression? Does it happen on v9 too?

[wolfgangwalther]

@wolfgangwalther Is this one a regression? Does it happen on v9 too?

I'm currently on pg14/pgrst9 - and it works fine there. This appeared when trying to upgrade. I tried both 10.0.0 and the current pre-release. Seems to be a regression from v9 to v10. Seems to be independent of pg14 vs pg15.

I haven't gone further, so far, but it should be simple to put the test case into the fixtures and bisect the commit where this started happening.

[wolfgangwalther]

git bisect tells me the first bad commit is d271942.

[wolfgangwalther]

The important bit seems to be the view v1. Every relationship that has v1 as either relTable or relForeignTable looks like this in the schema cache:

            "relColumns": [
              [
                "c1",
                "c2"
              ],
              [
                "c2",
                "c1"
              ]
            ],

All other relationships, including a, b and v2 only look like this:

            "relColumns": [
              [
                "c2",
                "c2"
              ],
              [
                "c1",
                "c1"
              ]
            ],

[wolfgangwalther]

One thing to note from my schema cache example above: In almost all cases the order of columns is c2, c1. Even though that works, it's technically incorrect. I would expect the schema cache to reflect the order of the foreign key in relColumns. Before the commit mentioned above, the column order in relColumns was always wrong. But at least consistent. I checked back to v8.