prisma: Upsert: `InterpretationError("Unable to convert expression result into a set of selection results", None)` with `relationMode = "prisma"` (starting with 4.6.0)

Bug description

2023-07-29T03:01:27.583Z prisma:client  Prisma Client call:
2023-07-29T03:01:27.583Z prisma:client  prisma.user.upsert({
  where: {
    email: "foobarbaz@gmail.com"
  },
  create: {
    email: "foobarbaz@gmail.com",
    name: "Teague Stockwell",
    profileSrc: "https://lh3.googleusercontent.com/a/foobarbaz"
  },
  update: {
    email: "foobarbaz@gmail.com",
    name: "Teague Stockwell",
    profileSrc: "https://lh3.googleusercontent.com/a/foobarbaz"
  }
})
2023-07-29T03:01:27.585Z prisma:client  Generated request:
2023-07-29T03:01:27.585Z prisma:client  {
  "modelName": "User",
  "action": "upsertOne",
  "query": {
    "arguments": {
      "where": {
        "email": "foobarbaz@gmail.com"
      },
      "create": {
        "email": "foobarbaz@gmail.com",
        "name": "Teague Stockwell",
        "profileSrc": "https://lh3.googleusercontent.com/a/foobarbaz"
      },
      "update": {
        "email": "foobarbaz@gmail.com",
        "name": "Teague Stockwell",
        "profileSrc": "https://lh3.googleusercontent.com/a/foobarbaz"
      }
    },
    "selection": {
      "$composites": true,
      "$scalars": true
    }
  }
}

2023-07-29T03:01:27.585Z prisma:client:libraryEngine  sending request, this.libraryStarted: true
2023-07-29T03:01:27.606Z prisma:client:request_handler  PrismaClientKnownRequestError: Query interpretation error. Error for binding '6': Error occurred during query execution:
InterpretationError("Unable to convert expression result into a set of selection results", None)
    at ko (/Users/teaguestockwell/src/buildable/node_modules/@prisma/client/runtime/library.js:26:6409)
    at _t.buildQueryError (/Users/teaguestockwell/src/buildable/node_modules/@prisma/client/runtime/library.js:104:902)
    at _t.request (/Users/teaguestockwell/src/buildable/node_modules/@prisma/client/runtime/library.js:103:4117)
    at async Object.singleLoader (/Users/teaguestockwell/src/buildable/node_modules/@prisma/client/runtime/library.js:122:6714)
    at async zr.request (/Users/teaguestockwell/src/buildable/node_modules/@prisma/client/runtime/library.js:122:7206)
    at async Object.jwt (webpack-internal:///(api)/./src/pages/api/auth/[...nextauth].ts:71:13)
    at async Object.callback (/Users/teaguestockwell/src/buildable/node_modules/next-auth/core/routes/callback.js:135:25)
    at async AuthHandler (/Users/teaguestockwell/src/buildable/node_modules/next-auth/core/index.js:201:28)
    at async NextAuthHandler (/Users/teaguestockwell/src/buildable/node_modules/next-auth/next/index.js:24:19)
    at async /Users/teaguestockwell/src/buildable/node_modules/next-auth/next/index.js:60:32
    at async Object.apiResolver (/Users/teaguestockwell/src/buildable/node_modules/next/dist/server/api-utils/node.js:372:9)
    at async DevServer.runApi (/Users/teaguestockwell/src/buildable/node_modules/next/dist/server/next-server.js:488:9)
    at async Object.fn (/Users/teaguestockwell/src/buildable/node_modules/next/dist/server/next-server.js:751:37)
    at async Router.execute (/Users/teaguestockwell/src/buildable/node_modules/next/dist/server/router.js:253:36)
    at async DevServer.run (/Users/teaguestockwell/src/buildable/node_modules/next/dist/server/base-server.js:384:29)
    at async DevServer.run (/Users/teaguestockwell/src/buildable/node_modules/next/dist/server/dev/next-dev-server.js:743:20)
    at async DevServer.handleRequest (/Users/teaguestockwell/src/buildable/node_modules/next/dist/server/base-server.js:322:20) {
  code: 'P2016',
  clientVersion: '5.0.0',
  meta: {
    details: "Error for binding '6': Error occurred during query execution:\n" +
      'InterpretationError("Unable to convert expression result into a set of selection results", None)'
  }
}
prisma:error 
Invalid `prisma.user.upsert()` invocation:


Query interpretation error. Error for binding '6': Error occurred during query execution:
InterpretationError("Unable to convert expression result into a set of selection results", None)
[next-auth][error][OAUTH_CALLBACK_HANDLER_ERROR] 
https://next-auth.js.org/errors#oauth_callback_handler_error 
Invalid `prisma.user.upsert()` invocation:


Query interpretation error. Error for binding '6': Error occurred during query execution:
InterpretationError("Unable to convert expression result into a set of selection results", None) {
  message: '\n' +
    'Invalid `prisma.user.upsert()` invocation:\n' +
    '\n' +
    '\n' +
    "Query interpretation error. Error for binding '6': Error occurred during query execution:\n" +
    'InterpretationError("Unable to convert expression result into a set of selection results", None)',
  stack: 'PrismaClientKnownRequestError: \n' +
    'Invalid `prisma.user.upsert()` invocation:\n' +
    '\n' +
    '\n' +
    "Query interpretation error. Error for binding '6': Error occurred during query execution:\n" +
    'InterpretationError("Unable to convert expression result into a set of selection results", None)\n' +
    '    at zr.handleRequestError (/Users/teaguestockwell/src/buildable/node_modules/@prisma/client/runtime/library.js:122:8308)\n' +
    '    at zr.handleAndLogRequestError (/Users/teaguestockwell/src/buildable/node_modules/@prisma/client/runtime/library.js:122:7697)\n' +
    '    at zr.request (/Users/teaguestockwell/src/buildable/node_modules/@prisma/client/runtime/library.js:122:7307)\n' +
    '    at async Object.jwt (webpack-internal:///(api)/./src/pages/api/auth/[...nextauth].ts:71:13)\n' +
    '    at async Object.callback (/Users/teaguestockwell/src/buildable/node_modules/next-auth/core/routes/callback.js:135:25)\n' +
    '    at async AuthHandler (/Users/teaguestockwell/src/buildable/node_modules/next-auth/core/index.js:201:28)\n' +
    '    at async NextAuthHandler (/Users/teaguestockwell/src/buildable/node_modules/next-auth/next/index.js:24:19)\n' +
    '    at async /Users/teaguestockwell/src/buildable/node_modules/next-auth/next/index.js:60:32\n' +
    '    at async Object.apiResolver (/Users/teaguestockwell/src/buildable/node_modules/next/dist/server/api-utils/node.js:372:9)\n' +
    '    at async DevServer.runApi (/Users/teaguestockwell/src/buildable/node_modules/next/dist/server/next-server.js:488:9)\n' +
    '    at async Object.fn (/Users/teaguestockwell/src/buildable/node_modules/next/dist/server/next-server.js:751:37)\n' +
    '    at async Router.execute (/Users/teaguestockwell/src/buildable/node_modules/next/dist/server/router.js:253:36)\n' +
    '    at async DevServer.run (/Users/teaguestockwell/src/buildable/node_modules/next/dist/server/base-server.js:384:29)\n' +
    '    at async DevServer.run (/Users/teaguestockwell/src/buildable/node_modules/next/dist/server/dev/next-dev-server.js:743:20)\n' +
    '    at async DevServer.handleRequest (/Users/teaguestockwell/src/buildable/node_modules/next/dist/server/base-server.js:322:20)',
  name: 'PrismaClientKnownRequestError'
}

How to reproduce

when a users signs in, the jwt callback from the file “[…nextauth].ts” upserts a user

  callbacks: {
    async jwt({token}) {
      const {email, name, picture} = token
      if (
        !email ||
        !name ||
        typeof email !== 'string' ||
        typeof name !== 'string' ||
        (typeof picture !== 'string' && typeof picture !== 'undefined')
      ) {
        throw new Error('malformed token')
      }
      const next = {email, name, profileSrc: picture}
      await client.user.upsert({
        where: {email},
        create: next,
        update: next,
      })
      return token
    },
  },

Expected behavior

  • upsert works as expected using 4.3.0 of @prisma/client and prisma
  • creating a user from prisma studio with the same data using 5.0.0 also works as expected

Prisma information

schema.prisma

datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma"
}

generator client {
  provider = "prisma-client-js"
}

model User {
  email  String @id @db.VarChar(256)
  userId String @unique @default(cuid()) @db.VarChar(64)

  name            String   @db.VarChar(128)
  createdAt       DateTime @default(now()) @db.DateTime(0)
  updatedAt       DateTime @updatedAt @db.DateTime(0)
  profileSrc      String?  @db.VarChar(128)
  profilePicS3Key String?  @db.VarChar(128)
  about           String?  @db.VarChar(256)
  profileViews    Int      @default(0) @db.UnsignedInt
  postViews       Int      @default(0) @db.UnsignedInt
  numFollowers    Int      @default(0) @db.UnsignedInt

  posts           Post[]      @relation("posted")
  comments        Comment[]   @relation("commented")
  likedComments   Comment[]   @relation("likedComments")
  likedPosts      Post[]      @relation("likedPosts")
  savedComments   Comment[]   @relation("savedComments")
  savedPosts      Post[]      @relation("savedPosts")
  topics          Topic[]     @relation("topics")
  usersFollowing  User[]      @relation("followingUsers")
  usersFollowedBy User[]      @relation("followingUsers")
  moderatesTopics Topic[]     @relation("moderatedTopics")
  pics            Pic[]
  rateLimits      RateLimit[]

  @@unique([email, userId])
  @@index([name])
  @@index([userId])
}

Environment & setup

  • OS: macos 12.6.3
  • Database: MySQL 8.0.27-1debian10
  • Node.js version: 18.14.0

Prisma Version

Environment variables loaded from .env
prisma                  : 5.0.0
@prisma/client          : 5.0.0
Current platform        : darwin
Query Engine (Node-API) : libquery-engine 6b0aef69b7cdfc787f822ecd7cdc76d5f1991584 (at node_modules/@prisma/engines/libquery_engine-darwin.dylib.node)
Schema Engine           : schema-engine-cli 6b0aef69b7cdfc787f822ecd7cdc76d5f1991584 (at node_modules/@prisma/engines/schema-engine-darwin)
Schema Wasm             : @prisma/prisma-schema-wasm 4.17.0-26.6b0aef69b7cdfc787f822ecd7cdc76d5f1991584
Default Engines Hash    : 6b0aef69b7cdfc787f822ecd7cdc76d5f1991584
Studio                  : 0.487.0
Preview Features        : referentialIntegrity

About this issue

  • Original URL
  • State: open
  • Created a year ago
  • Reactions: 10
  • Comments: 18 (5 by maintainers)

Most upvoted comments

We also hit this error when attempting to upgrade from Prisma 4.14 to 5.3 on Postgres:

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)

Unfortunately, it only occurs during a very complex, deeply nested upsert of nearly 5000 lines of formatted JSON. We’re still trying to narrow it down.

+2
trevorr on Sep 22, 2023

@janpio @yovanoc I created #21182 for not using relationMode=prisma.

+1
trevorr on Sep 21, 2023

I’m not using it so I will wait the other issue , I’m using Postgres 15 on docker and im on Mac

+1
yovanoc on Sep 22, 2023

Ok, that made me confirm that @teaguestockwell’s problem is triggered by relationMode = "prisma". When that is removed from the schema, and prisma generate is run, you can run yarn upsert multiple times without it triggering the problem. The generated SQL queries then look like this:

prisma:query BEGIN
prisma:query SELECT `db`.`User`.`email` FROM `db`.`User` WHERE (`db`.`User`.`email` = ? AND 1=1)
prisma:query SELECT `db`.`User`.`email` FROM `db`.`User` WHERE (`db`.`User`.`email` = ? AND 1=1)
prisma:query UPDATE `db`.`User` SET `email` = ?, `name` = ?, `profileSrc` = ?, `updatedAt` = ? WHERE (`db`.`User`.`email` IN (?) AND (`db`.`User`.`email` = ? AND 1=1))
prisma:query SELECT `db`.`User`.`email`, `db`.`User`.`userId`, `db`.`User`.`name`, `db`.`User`.`createdAt`, `db`.`User`.`updatedAt`, `db`.`User`.`profileSrc`, `db`.`User`.`profilePicS3Key`, `db`.`User`.`about`, `db`.`User`.`profileViews`, `db`.`User`.`postViews`, `db`.`User`.`numFollowers` FROM `db`.`User` WHERE `db`.`User`.`email` = ? LIMIT ? OFFSET ?
prisma:query COMMIT

@trevorr That tells me that you have a different problem than Teague. Optimally open a new issue and mention explicity that you are not using relationMode=prisma. But please provide some information on which database you are using, OS, and so no - all the things that could have an impact here.

@yovanoc @nykolaslima Are you using relationMode=prisma or not? Depending on your answer, you should comment on the new issue that Trevor will create.

+1
janpio on Sep 21, 2023
Read more comments on GitHub
← prisma: Upsert errors with compound unique key with Date on MySQL: `Query ... is required to return data, but found no record(s)`
prisma: `upsert()` `where` is defined as `number | undefined` but does not allow `undefined` to not select anything →