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

Most upvoted comments

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.

Looks like it is still broken on 5.6.0 just so no one else needs to try it out.

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. 🤔 😭

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?

@trevorr @def-ilya Does your query maybe also update two of the same models at the same time?

@janpio Confirming that it does in fact update two of the same models at the same time 😃

Can you confirm that?

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

Could you try to create a new app with this code and schema snippet and see if you can reproduce it this way? If you can’t, we would also not be able to and hence would spendour time trying to make something happen, that can not happen.

Thanks Jan.

  1. created new node-ts project
  2. init’d Prisma
  3. created new remote DB instance
  4. migrated schema from our project to sandbox DB
  5. created example update function that is identical in function to what we’re trying to do
  6. runs without issue, correct output

Strange. Going to have a play around with swapping dev DB.