Query optimization is not compatible with PostgreSQL citext fields

[murar8]

Bug description

As for https://www.prisma.io/docs/orm/prisma-client/queries/query-optimization-performance#solving-n1-in-graphql-with-findunique-and-prisma-clients-dataloader, the prisma engine will optimize multiple findUnique queries that happen in the same tick by batching the requests into a single query. This is a problem when a postgres model includes a citext field, which is case insensitive when applying the = SQL operator, but not when using the in operator.

How to reproduce

Will attach a minimal reproducible example as soon as I have some time.

Expected behavior

Ideally, findUnique will disable the optimization only for citext fields.

Prisma information

Will attach a minimal reproducible example as soon as I have some time.

Environment & setup

• OS: Fedora 40 x64
• Database: PostgreSQL 16
• Node.js version: 20 LTS

Prisma Version

prisma                  : 5.3.1
@prisma/client          : 5.3.1
Current platform        : debian-openssl-3.0.x
Query Engine (Node-API) : libquery-engine 61e140623197a131c2a6189271ffee05a7aa9a59 (at node_modules/@prisma/engines/libquery_engine-debian-openssl-3.0.x.so.node)
Schema Engine           : schema-engine-cli 61e140623197a131c2a6189271ffee05a7aa9a59 (at node_modules/@prisma/engines/schema-engine-debian-openssl-3.0.x)
Schema Wasm             : @prisma/prisma-schema-wasm 5.3.1-2.61e140623197a131c2a6189271ffee05a7aa9a59
Default Engines Hash    : 61e140623197a131c2a6189271ffee05a7aa9a59
Studio                  : 0.494.0

[Druue]

Hey @murar8! Could you please elaborate on what issue specifically you're running into with batching and citext?

We had another issue regarding #13534 where batched findUniques with a where on citext fields yielded incorrect results, and so we disabled batching when encountering citext in the following PR which was part of our v5.13.0 release.

I see that you mentioned that you're still on v5.3.1, if the above is the same issue that you're running into, could you please confirm whether upgrading resolves the issue?

[murar8]

Yep, should have searched more, I can't test right now but that looks like the same problem. I will reopen the issue if I have any issues after upgrading. Thanks!