Change in query generation related to case insensitive 'in' #29215
Description
Bug description
After upgrading to Prisma 7.4.0, queries involving in: [...] with case: 'insensitive' appear to have changed, and likely have broken.
Severity
Reproduction
Run a query similar to below (in: ['a', 'b'] with case: 'insensitive')
Expected vs. Actual Behavior
I'd expect the generated query not to change from a minor version upgrade
Frequency
Consistently reproducible
Does this occur in development or production?
Both development and production
Is this a regression?
Yes, worked in 7.3.0 and broke in 7.4.0
Workaround
Downgrading to 7.3.0 fixes it
Prisma Schema & Queries
model Attachment {
id Int @id @default(autoincrement())
createdAt DateTime @default(now())
updatedAt DateTime @default(now()) @updatedAt
name String?
fileName String
originalFileName String
// where we're storing the file in S3
fileUrl String?
companyId Int
company Company @relation(fields: [companyId], references: [id])
@@index([fileName])
}await prisma.attachment.findMany({
where: {
companyId: 34,
fileName: {
in: ['AbC.jpg', 'DEF.txt', 'gHi.png'],
mode: 'insensitive',
},
},
select: {
id: true,
fileName: true,
},
});Prisma Config
import 'dotenv/config';
import { defineConfig, env } from 'prisma/config';
export default defineConfig({
schema: 'prisma/schema.prisma',
migrations: {
path: 'prisma/migrations',
seed: 'tsx prisma/seed.ts',
},
datasource: {
url: env('DATABASE_URL'),
},
});Logs & Debug Info
v7.3.0:
SELECT "public"."Attachment"."id", "public"."Attachment"."fileName" FROM "public"."Attachment"
WHERE ("public"."Attachment"."companyId" = $1
AND LOWER("public"."Attachment"."fileName") IN (LOWER($2),LOWER($3),LOWER($4)))
OFFSET $5
v7.4.0:
SELECT "public"."Attachment"."id", "public"."Attachment"."fileName" FROM "public"."Attachment"
WHERE ("public"."Attachment"."companyId" = $1
AND LOWER("public"."Attachment"."fileName") IN ($2,$3,$4)) OFFSET $5
Environment & Setup
- OS: macOS
- Database: PostgreSQL
- Node.js version: v22.21.1
Prisma Version
Loaded Prisma config from prisma.config.ts.
Prisma schema loaded from prisma/schema.prisma.
prisma : 7.4.0
@prisma/client : 7.4.0
Operating System : darwin
Architecture : arm64
Node.js : v22.21.1
TypeScript : 5.9.3
Query Compiler : enabled
PSL : @prisma/prisma-schema-wasm 7.4.0-20.ab56fe763f921d033a6c195e7ddeb3e255bdbb57
Schema Engine : schema-engine-cli ab56fe763f921d033a6c195e7ddeb3e255bdbb57 (at node_modules/prisma/node_modules/@prisma/engines/schema-engine-darwin-arm64)
Default Engines Hash : ab56fe763f921d033a6c195e7ddeb3e255bdbb57
Studio : 0.13.1
Preview Features : postgresqlExtensions, typedSql