relationJoins: Broken query with nested filter in many-to-many through one-to-many relation

[aqrln]

@key-moon wrote in #22288 (comment):

Hey. Today, I encountered a bug that this feature flag seems to have caused.

Following a query with a schema like A <-1--N-> B <-N--N-> C generates an invalid SQL Query, and it causes a The column t3.name does not exist in the current database. error.

await prisma.a.findFirst({
  select: {
    bs: {
      where: {
        cs: {
          every: {
            name: "a"
          },
        },
      }
    }
  }
});

Client Error

PrismaClientKnownRequestError: 
Invalid `prisma.a.findFirst()` invocation in
./script.ts:6:18

  3 const prisma = new PrismaClient()
  4 
  5 async function main() {
→ 6   await prisma.a.findFirst(
The column `t3.name` does not exist in the current database.
    at Ln.handleRequestError (./node_modules/.pnpm/@prisma+client@5.19.0_prisma@5.19.0/node_modules/@prisma/client/runtime/library.js:121:7753)
    at Ln.handleAndLogRequestError (./node_modules/.pnpm/@prisma+client@5.19.0_prisma@5.19.0/node_modules/@prisma/client/runtime/library.js:121:7061)
    at Ln.request (./node_modules/.pnpm/@prisma+client@5.19.0_prisma@5.19.0/node_modules/@prisma/client/runtime/library.js:121:6745)
    at async l (./node_modules/.pnpm/@prisma+client@5.19.0_prisma@5.19.0/node_modules/@prisma/client/runtime/library.js:130:9633) {
  code: 'P2022',
  clientVersion: '5.19.0',
  meta: { modelName: 'A', column: 't3.name' }
}

DB Error

ERROR:  column t3.name does not exist at character 296
STATEMENT:  SELECT "t1"."id", "A_bs"."__prisma_data__" AS "bs" FROM "public"."A" AS "t1" LEFT JOIN LATERAL (SELECT COALESCE(JSONB_AGG("__prisma_data__"), '[]') AS "__prisma_data__" FROM (SELECT "t4"."__prisma_data__" FROM (SELECT JSONB_BUILD_OBJECT('id', "t3"."id", 'aId', "t3"."aId") AS "__prisma_data__", "t3"."name" FROM (SELECT "t2".* FROM "public"."B" AS "t2" WHERE "t1"."id" = "t2"."aId" /* root select */) AS "t3" /* inner select */) AS "t4" WHERE ("t4"."id") NOT IN (SELECT "t1"."A" FROM "public"."_BToC" AS "t1" INNER JOIN "public"."C" AS "j1" ON ("j1"."id") = ("t1"."B") WHERE ((NOT "j1"."name" = $1) AND "t1"."A" IS NOT NULL)) /* middle select */) AS "t5" /* outer select */) AS "A_bs" ON true LIMIT $2

I've created a PoC repository, so I would appreciate it if you could take a look.

https://github.com/key-moon/prisma-relationjoins-bug-reproduce

Thank you!

The rendered query looks like this:

SELECT
  "t1"."id",
  "A_bs"."__prisma_data__" AS "bs"
FROM
  "public"."A" AS "t1"
  LEFT JOIN LATERAL (
    SELECT
      COALESCE(JSONB_AGG("__prisma_data__"), '[]') AS "__prisma_data__"
    FROM
      (
        SELECT
          "t4"."__prisma_data__"
        FROM
          (
            SELECT
              JSONB_BUILD_OBJECT('id', "t3"."id", 'aId', "t3"."aId") AS "__prisma_data__",
              "t3"."name"
            FROM
              (
                SELECT
                  "t2".*
                FROM
                  "public"."B" AS "t2"
                WHERE
                  "t1"."id" = "t2"."aId"
                  /* root select */
              ) AS "t3"
              /* inner select */
          ) AS "t4"
        WHERE
          ("t4"."id") NOT IN (
            SELECT
              "t1"."A"
            FROM
              "public"."_BToC" AS "t1"
              INNER JOIN "public"."C" AS "j1" ON ("j1"."id") = ("t1"."B")
            WHERE
              (
                (NOT "j1"."name" = $1)
                AND "t1"."A" IS NOT NULL
              )
          )
          /* middle select */
      ) AS "t5"
      /* outer select */
  ) AS "A_bs" ON true
LIMIT
  $2

The "t3"."name" selection is bogus, the column does not exist in this table (it is a field of C model, not B) and it's not actually used further in the query.

[aqrln]

#25103 hinted that it might have been a regression in 5.19. Trying with 5.18, it appears to still be broken but in a completely different way:

prisma:query SELECT
  "t1"."id",
  "A_bs"."__prisma_data__" AS "bs"
FROM
  "public"."A" AS "t1"
  LEFT JOIN LATERAL (
    SELECT
      COALESCE(JSONB_AGG("__prisma_data__"), '[]') AS "__prisma_data__"
    FROM
      (
        SELECT
          "t4"."__prisma_data__"
        FROM
          (
            SELECT
              JSONB_BUILD_OBJECT('id', "t3"."id", 'aId', "t3"."aId") AS "__prisma_data__"
            FROM
              (
                SELECT
                  "t2".*
                FROM
                  "public"."B" AS "t2"
                WHERE
                  "t1"."id" = "t2"."aId"
                  /* root select */
              ) AS "t3"
              /* inner select */
          ) AS "t4"
        WHERE
          ("t4"."id") NOT IN (
            SELECT
              "t1"."A"
            FROM
              "public"."_BToC" AS "t1"
              INNER JOIN "public"."C" AS "j1" ON ("j1"."id") = ("t1"."B")
            WHERE
              (
                (NOT "j1"."name" = $1)
                AND "t1"."A" IS NOT NULL
              )
          )
          /* middle select */
      ) AS "t5"
      /* outer select */
  ) AS "A_bs" ON true
LIMIT
  $2
PrismaClientKnownRequestError:
Invalid `prisma.a.findFirst()` invocation in
/Users/aqrln/prisma/prisma-relationjoins-bug-reproduce/script.ts:8:18

  5 })
  6
  7 async function main() {
→ 8   await prisma.a.findFirst(
The column `t4.id` does not exist in the current database.
    at _n.handleRequestError (/Users/aqrln/prisma/prisma-relationjoins-bug-reproduce/node_modules/.pnpm/@prisma+client@5.18.0_prisma@5.18.0/node_modules/@prisma/client/runtime/library.js:121:7749)
    at _n.handleAndLogRequestError (/Users/aqrln/prisma/prisma-relationjoins-bug-reproduce/node_modules/.pnpm/@prisma+client@5.18.0_prisma@5.18.0/node_modules/@prisma/client/runtime/library.js:121:7057)
    at _n.request (/Users/aqrln/prisma/prisma-relationjoins-bug-reproduce/node_modules/.pnpm/@prisma+client@5.18.0_prisma@5.18.0/node_modules/@prisma/client/runtime/library.js:121:6741)
    at async l (/Users/aqrln/prisma/prisma-relationjoins-bug-reproduce/node_modules/.pnpm/@prisma+client@5.18.0_prisma@5.18.0/node_modules/@prisma/client/runtime/library.js:130:9355)
    at async main (/Users/aqrln/prisma/prisma-relationjoins-bug-reproduce/script.ts:8:3) {
  code: 'P2022',
  clientVersion: '5.18.0',
  meta: { modelName: 'A', column: 't4.id' }
}

[jkomyno]

prisma/prisma-engines#4968 may be the responsible PR here.

[aqrln]

@jkomyno the issue in the second comment that happens on 5.18 is still a separate bug though. Or was it exactly the issue that was fixed in prisma/prisma-engines#4968?

[key-moon]

@aqrln I guess its a separate bug. Removing name from PoC removes the regression error, and we can see that the t4.id error is still present.

key-moon/prisma-relationjoins-bug-reproduce@0959065

Invalid `prisma.a.findFirst()` invocation in
./script.ts:6:18

  3 const prisma = new PrismaClient()
  4 
  5 async function main() {
→ 6   await prisma.a.findFirst(
The column `t4.id` does not exist in the current database.
    at Ln.handleRequestError (./node_modules/.pnpm/@prisma+client@5.19.0_prisma@5.19.0/node_modules/@prisma/client/runtime/library.js:121:7753)
    at Ln.handleAndLogRequestError (./node_modules/.pnpm/@prisma+client@5.19.0_prisma@5.19.0/node_modules/@prisma/client/runtime/library.js:121:7061)
    at Ln.request (./node_modules/.pnpm/@prisma+client@5.19.0_prisma@5.19.0/node_modules/@prisma/client/runtime/library.js:121:6745)
    at async l (./node_modules/.pnpm/@prisma+client@5.19.0_prisma@5.19.0/node_modules/@prisma/client/runtime/library.js:130:9633) {
  code: 'P2022',
  clientVersion: '5.19.0',
  meta: { modelName: 'A', column: 't4.id' }
}

SELECT 
  "t1"."id", 
  "A_bs"."__prisma_data__" AS "bs" 
FROM 
  "public"."A" AS "t1" 
  LEFT JOIN lateral (
    select 
      COALESCE(
        jsonb_agg("__prisma_data__"), 
        '[]'
      ) AS "__prisma_data__" 
    FROM 
      (
        SELECT 
          "t4"."__prisma_data__" 
        FROM 
          (
            SELECT 
              jsonb_build_object(
                'id', "t3"."id", 'aId', "t3"."aId"
              ) AS "__prisma_data__" 
            FROM 
              (
                SELECT 
                  "t2".* 
                FROM 
                  "public"."B" AS "t2" 
                WHERE 
                  "t1"."id" = "t2"."aId" 
                  /* root select */
                  ) AS "t3" 
              /* inner select */
              ) AS "t4" 
        WHERE 
          ("t4"."id") NOT IN (
            SELECT 
              "t1"."A" 
            FROM 
              "public"."_BToC" AS "t1" 
              INNER JOIN "public"."C" AS "j1" ON ("j1"."id") = ("t1"."B") 
            WHERE 
              (
                (NOT 1 = 1) 
                AND "t1"."A" IS NOT NULL
              )
          ) 
          /* middle select */
          ) AS "t5" 
      /* outer select */
      ) AS "A_bs" ON true 
limit 
  $1

[aqrln]

Fixed in prisma/prisma-engines#4994 (including the bug that was reproducible on 5.18), will be released in 5.19.1.