Off-by-one error when combining where with some #28968
Description
Bug description
I've found a strange off-by-one error when using .count() with a where clause. When I log the query and execute it myself I get the correct result.
// Create 1 organization
await prisma.organization.create({
data: {
name: "Acme",
types: {
createMany: {
data: [{ type: 1 }, { type: 10 }],
},
},
},
});
// Count with a where including an `in`
const count = await prisma.organization.count({
where: {
types: {
some: {
type: {
in: [1, 10],
},
},
},
},
});
console.log(
"Organizations with types 1 or 10 (expected 1):",
count // actual is 2
);Severity
🔹 Minor: Unexpected behavior, but does not block development
Reproduction
https://github.com/nicojs/prisma-bug
Expected vs. Actual Behavior
I would expect to see a result of 1, I got 2
Frequency
Consistently reproducible
Does this occur in development or production?
Both development and production
Is this a regression?
Yes, it did work in Prisma 5.x
Workaround
Using .groupBy({ by: ['id'] }), but it not really when dealing with large datasets.
Prisma Schema & Queries
datasource db {
provider = "sqlite"
}
generator client {
provider = "prisma-client"
output = "../generated/prisma"
}
model Organization {
id Int @id @default(autoincrement())
name String @unique
types OrganizationType[]
}
model OrganizationType {
organizationId Int
type Int
organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
@@id([organizationId, type])
}await prisma.organization.create({
data: {
name: "Acme",
types: {
createMany: {
data: [{ type: 1 }, { type: 10 }],
},
},
},
});
const count = await prisma.organization.count({
where: {
types: {
some: {
type: {
in: [1, 10],
},
},
},
},
});
console.log(
"Organizations with types 1 or 10 (expected 1):",
count // actual: 2
);Prisma Config
// This file was generated by Prisma, and assumes you have installed the following:
// npm install --save-dev prisma dotenv
import "dotenv/config";
import { defineConfig } from "prisma/config";
export default defineConfig({
schema: "prisma/schema.prisma",
migrations: {
path: "prisma/migrations",
},
datasource: {
url: "file:./dev.db",
},
});Logs & Debug Info
Generated with
const prisma = new PrismaClient({
adapter,
log: [
{
emit: "event",
level: "query",
},
],
});
prisma.$on("query", (e) => {
console.log(`${e.query} ${e.params}`);
});Logs:
INSERT INTO `main`.`Organization` (`name`) VALUES (?) RETURNING `id` AS `id` ["Acme"]
INSERT INTO `main`.`OrganizationType` (`organizationId`, `type`) VALUES (?,?), (?,?) ["1","1","1","10"]
SELECT `main`.`Organization`.`id`, `main`.`Organization`.`name` FROM `main`.`Organization` WHERE `main`.`Organization`.`id` = ? LIMIT ? OFFSET ? ["1","1","0"]
COMMIT []
SELECT COUNT(*) AS `_count$_all` FROM (SELECT `main`.`Organization`.`id` FROM `main`.`Organization` WHERE EXISTS(SELECT `t0`.`organizationId` FROM `main`.`OrganizationType` AS `t0` WHERE (`t0`.`type` IN (?,?) AND (`main`.`Organization`.`id`) = (`t0`.`organizationId`) AND `t0`.`organizationId` IS NOT NULL)) LIMIT ? OFFSET ?) AS `sub` ["1","10",-1,"0"]
Organizations with types 1 or 10 (expected 1): 2
Environment & Setup
- OS: macOS
- Database: SQLite
- Node.js version: 22.18
Prisma Version
// Prisma version output
Loaded Prisma config from prisma.config.ts.
Prisma schema loaded from prisma/schema.prisma.
prisma : 7.2.0
@prisma/client : 7.2.0
Operating System : darwin
Architecture : arm64
Node.js : v22.18.0
TypeScript : unknown
Query Compiler : enabled
PSL : @prisma/prisma-schema-wasm 7.2.0-4.0c8ef2ce45c83248ab3df073180d5eda9e8be7a3
Schema Engine : schema-engine-cli 0c8ef2ce45c83248ab3df073180d5eda9e8be7a3 (at node_modules/@prisma/engines/schema-engine-darwin-arm64)
Default Engines Hash : 0c8ef2ce45c83248ab3df073180d5eda9e8be7a3
Studio : 0.9.0