Including a subset of children throws error if more than ~32767 children exist

[fmorett]

Bug description

If a parent has more than 30192 children and in an include statement only a subset is selected, the following error is thrown:

too many bind variables in prepared statement, expected maximum of 32767, received 32770

How to reproduce

Check out this repo for a reproduction:

https://github.com/Fibs7000/prisma-bug-to-many-bind-variables

1. Create a schema with a many to many relation
2. add 1 parent and add more than 32767 children to that parent
3. try querying the parent and include one or more of the children (also direct id does not work)

Expected behavior

It should just return the three selected items.

Prisma information

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider        = "prisma-client-js"
}


model A {
  id Int @id @default(autoincrement())
  B  B[]
}

model B {
  id  Int @id @default(autoincrement())
  A   A[]
}

import { PrismaClient } from '@prisma/client';


const prisma = new PrismaClient();

async function main() {
    // cleanup old data
    await prisma.a.deleteMany();
    await prisma.b.deleteMany();
    // create one parent
    await prisma.a.create({
        data: {
            id: 1
        }
    });
    // create 100k children
    await prisma.$executeRaw`
        INSERT INTO "B" (id)
        SELECT generate_series(1, 100000);
    `;
    // connect 100k children to parent
    await prisma.$executeRaw`
        INSERT INTO "_AToB" ("A", "B")
        SELECT 1, generate_series(1, 100000);
    `;

    // this throws the following error:
    /*
     await prisma.a.findUnique(
Assertion violation on the database: `too many bind variables in prepared statement, expected maximum of 32767, received 32770`
    at Rn.handleRequestError (node_modules/@prisma/client/runtime/library.js:174:7325)
    at Rn.handleAndLogRequestError (node_modules/@prisma/client/runtime/library.js:174:6754)
    at Rn.request (node_modules/@prisma/client/runtime/library.js:174:6344) {
  code: 'P2035',
  clientVersion: '4.16.0',
  meta: {
    database_error: 'too many bind variables in prepared statement, expected maximum of 32767, received 32770'
  }
}
     */
    await prisma.a.findUnique({
        where: {
            id: 1
        },
        include: {
            B: {
                where: {
                    id: {
                        in: [1,2,3]
                    }
                }
            }
        }
    });
    // this also does not work
    await prisma.a.findUnique({
        where: {
            id: 1
        },
        include: {
            B: {
                where: {
                    id: 1
                }
            }
        }
    });
}

main().catch((e) => {
    console.error(e);
})

Environment & setup

• OS: macOS
• Database: PostgreSQL
• Node.js version: v16.15.0

Prisma Version

prisma                  : 4.16.0
@prisma/client          : 4.16.0
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine b20ead4d3ab9e78ac112966e242ded703f4a052c (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine        : migration-engine-cli b20ead4d3ab9e78ac112966e242ded703f4a052c (at node_modules/@prisma/engines/migration-engine-darwin-arm64)
Format Wasm             : @prisma/prisma-fmt-wasm 4.16.0-66.b20ead4d3ab9e78ac112966e242ded703f4a052c
Default Engines Hash    : b20ead4d3ab9e78ac112966e242ded703f4a052c
Studio                  : 0.484.0

[janpio]

Thanks for opening the issue @Fibs7000. I can confirm the bug, but am not sure why the uppercase BREAKING. This also seems to be broken the same way in 4.15.0 or - even older - 4.10.0. Do you agree you are just reporting an older bug?

[fmorett]

True. Yes i've originally discovered this in 3.10 so it seems to be broken a long time already

[DamodarSojka]

I am also observing this in 4.12.0.

[erwich2]

I am observing this bug in 5.7.1 as well. Is there any kind of workaround to force chunking in this circumstance? I figure since this has been a bug since 3.10 or beyond, there might be some other way to structure queries in this way to to avoid the maximum bind parameter bug when chunking doesn't happen?

[Weakky]

Hey folks,

We have spotted this issue as potentially being solved by the new relationJoins preview feature, which uses JOINs to resolve relations in a single query.

If you have the time, we'd love for you to try it out and let us know if it has indeed fixed your issue. You can learn more about it on our release notes here.

Thank you 🙏

[fmorett]

Yes this indeed fixed the issue. The testcase I wrote in the beginning does not break with the v 5.9.1

[aqrln]

Hi @Fibs7000! We have added support for native database JOINs for PostgreSQL and CockroachDB under the relationJoins preview feature in Prisma 5.7.0, and today, in Prisma 5.10.0, we have released support for relationJoins based on correlated subqueries in MySQL 8.0.14 and newer. This should prevent this type of issues.

Does updating to the latest Prisma and enabling the relationJoins preview feature fix this problem for you?

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["relationJoins"]
}

Thank you in advance for following up 🙏