prisma: Update: `InterpretationError("Unable to convert expression result into a set of selection results", None)` (starting with 5.2.0)
Bug description
After upgrading from Prisma 4.16.2 to 5.3.0, performing a complex update with many nested upsert and connectOrCreate results in the following error:
PrismaClientKnownRequestError:
Invalid `prisma.loan.update()` invocation:
Query interpretation error. Error for binding '603': Error occurred during query execution:
InterpretationError("Unable to convert expression result into a set of selection results", None)
at wn.handleRequestError (.../packages/prisma-client/src/__generated__/PrismaClient/runtime/library.js:123:6730)
at wn.handleAndLogRequestError (.../packages/prisma-client/src/__generated__/PrismaClient/runtime/library.js:123:6119)
at wn.request (.../packages/prisma-client/src/__generated__/PrismaClient/runtime/library.js:123:5839)
at async l (.../packages/prisma-client/src/__generated__/PrismaClient/runtime/library.js:128:9763)
This issue seemed to be the same as #20448, but that issue involves relationMode = "prisma" and MySQL, whereas this issue occurs with foreign keys and PostgreSQL.
How to reproduce
I don’t have a reproduction to share, since our schema has 229 models, and this update uses many of them. The generated update JSON is almost 5000 lines. Here is an trimmed down version representative of the whole:
{
"where": { "id": "bce93061-b5fa-4b4c-9242-687fe7993f75" },
"data": {
"baseLoanAmount": 656175419,
"loanDownPayments": {
"upsert": [
{
"where": { "id": "7733e420-1396-4c4e-885e-9b7b8446119a" },
"update": { "giftTransferDate": "2020-01-01T00:00:00.000Z" },
"create": {
"id": "7733e420-1396-4c4e-885e-9b7b8446119a",
"amount": 492700,
"giftTransferDate": "2020-01-01T00:00:00.000Z"
/* ... */
}
},
{
"where": { "id": "fdffe05e-b8a6-496b-af5a-26bf03f214cb" },
"update": { "giftTransferDate": "2023-12-24T00:00:00.000Z" },
"create": {
"id": "fdffe05e-b8a6-496b-af5a-26bf03f214cb",
"amount": 738573900,
"giftTransferDate": "2023-12-24T00:00:00.000Z"
/* ... */
}
}
]
},
"loanProduct": { /* ... */ },
"loanPurchaseCredits": { /* ... */ },
"loanFees": { /* ... */ },
"loanPrepaidFees": { /* ... */ },
"loanEscrowFees": { /* ... */ },
"loanParties": { /* ... */ },
"subjectProperty": {
"upsert": {
"update": {
"propertyGroundLeaseExpiredAt": "2012-01-01T00:00:00.000Z"
},
"create": {
"attachmentType": "Attached",
"propertyProject": {
"connectOrCreate": {
"where": { "id": "112e6a59-4117-488a-b4a1-3135f52ffd7e" },
"create": {
"name": "Community Name",
"designType": "GardenProject",
"id": "112e6a59-4117-488a-b4a1-3135f52ffd7e"
}
}
},
"propertyGroundLeaseExpiredAt": "2012-01-01T00:00:00.000Z",
/* ... */
"id": "bc216582-1591-4113-902e-d22efaa0ac3b"
}
}
},
"monthlyPAndI": null
}
}
Expected behavior
The update should succeed, as it did against numerous Prisma 4.x versions.
Prisma information
generator client {
provider = "prisma-client-js"
previewFeatures = ["tracing"]
output = "./__generated__/PrismaClient"
binaryTargets = ["native", "linux-musl", "linux-musl-openssl-3.0.x"]
}
generator jsonSchema {
provider = "prisma-json-schema-generator"
output = "./__generated__/PrismaClient"
keepRelationScalarFields = "true"
}
datasource postgresql {
provider = "postgresql"
url = env("DATABASE_URL")
}
Environment & setup
- OS: macOS 13.5.2
- Database: PostgreSQL 15.2
- Node.js version: 16.20.0
Prisma Version
prisma : 5.3.0
@prisma/client : 5.3.0
Current platform : darwin-arm64
Query Engine (Node-API) : libquery-engine e90b936d84779543cbe0e494bc8b9d7337fad8e4 (at ../../node_modules/.pnpm/@prisma+engines@5.3.0/node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Schema Engine : schema-engine-cli e90b936d84779543cbe0e494bc8b9d7337fad8e4 (at ../../node_modules/.pnpm/@prisma+engines@5.3.0/node_modules/@prisma/engines/schema-engine-darwin-arm64)
Schema Wasm : @prisma/prisma-schema-wasm 5.3.0-36.e90b936d84779543cbe0e494bc8b9d7337fad8e4
Default Engines Hash : e90b936d84779543cbe0e494bc8b9d7337fad8e4
Studio : 0.494.0
About this issue
- Original URL
- State: closed
- Created 9 months ago
- Reactions: 17
- Comments: 34 (6 by maintainers)
Commits related to this issue
- build(deps,back): upgrade Prisma to 5.1 We can't get to latest due to a regression from Prisma 5.2 that still hasn't been fixed. https://github.com/prisma/prisma/issues/21182 — committed to momentum-mod/website by tsa96 7 months ago
- fix query error, but can't use nested upsert due to regression in prisma v5.6.0 related to this issue #prisma/prisma#21182 — committed to AnthonyZhang220/nextjs-social-app by AnthonyZhang220 7 months ago
- fix(qe): make `UpdateRecord::WithSelection` nodes return `RecordSelection` (#4508) Query graph nodes that are not result nodes and fulfill the requirements of other nodes don't inherently need names ... — committed to prisma/prisma-engines by aqrln 7 months ago
Please, pardon me if I sound harsh.
One of the main features and the main way to do transactions in Prisma is “Nested writes” and now it is effectively broken for non-trivial cases. Broken in runtime without any type’s warning.
Am I misjudging it or it is the biggest issue possible, a literal show-stopper?
I’m worried that the more new versions without a fix are released the more people are going to bump into this problem the hard way.
I can confirm that 5.1.0 works as well!
Looks like it is still broken on 5.6.0 just so no one else needs to try it out.
Not actively, but as it is a regression, it has high priority. Can’t give you an ETA though. Might be 3 weeks, 6 weeks or longer.
Yeah, it’s absolutely broken in 5.6.0. One more super simple bug reproduction https://github.com/wanjas/prisma-nested-updates-bug
If it broke serverless databases, I bet it would get a lot more attention. 🤔 😭
https://github.com/yovanoc/prisma-repro
managed to get a repro guys @janpio @trevorr ❤️
I cry every time I see “Requires Prisma 5.2+” 😦
Oh, that’s sad. This is the only thing holding us back from enjoying the latest features 😕
Hopefully, it gets fixed soon. Congrats to the team!
Yeah, the issue is still there in 5.5.0
Anyone try 5.5.0 yet?
@janpio Confirming that it does in fact update two of the same models at the same time 😃
Yes!
@trevorr 🫡 Godspeed. Try delete one nested query and see if your bind # drops? If it does, it looks like you might have to get counting lol
Thanks Jan.
Strange. Going to have a play around with swapping dev DB.