bigint(20) UNSIGNED got signed bigint bound error

[52cs]

Bug description

Mysql:

CREATE TABLE `labels`  (.  
  `id`                bigint(20) UNSIGNED AUTO_INCREMENT NOT NULL,
  PRIMARY KEY(`id`)
)

Prisma schema:

model labels {
  id               BigInt             @id @default(autoincrement()) @db.UnsignedBigInt
}

unsigned BigInt range should be [0, BigInt('18446744073709551615')]

use id = BigInt('11111111111111111111')

Got Error:

{
  "code": "P2033",
  "meta": {
    "details": "Query parsing failure: A number used in the query does not fit into a 64 bit **signed** integer. Consider using `BigInt` as field type if you're trying to store large integers."
},
}

Expecdted:
Not error throw if it use unsigned instead of signed internally.

How to reproduce

Expected behavior

Expecdted:
Not error throw if it use unsigned instead of signed internally.

Prisma information

Prisma schema:

model labels {
  id               BigInt             @id @default(autoincrement()) @db.UnsignedBigInt
}

Environment & setup

• OS: Ubuntu 20.04
• Database: Mysql 8
• Node.js version: 18.7.0

Prisma Version

{
  "prisma": "^4.1.0",
  "@prisma/client": "^4.1.0",
}

[janpio]

What kind of Prisma Client query are you trying to execute? Sharing that would help a lot with a reproduction on our side.

[52cs]

Prisma Client query:

    const rec = await this.prisma.labels.findUnique({
      where: {
        id: BigInt("11111111111111111111"),
      },  
    }); 

Got Exception

{
  "code": "P2033",
  "meta": {
    "details": "Query parsing failure: A number used in the query does not fit into a 64 bit signed integer. Consider using `BigInt` as field type if you're trying to store large integers."
  },
}

Test with native mysql:

mysql> select * from labels where id=11111111111111111111;
Empty set (0.00 sec)

Note:
@db.UnsignedBigInt
2^64 - 1 = 18446744073709551615
18446744073709551615 / 2 < 11111111111111111111 < 18446744073709551615

[pimeys]

Still a problem with 4.3.1.

Debug logs from the reproduction:

  prisma:tryLoadEnv  Environment variables loaded from /prisma-14632/.env +0ms
  prisma:tryLoadEnv  Environment variables loaded from /prisma-14632/.env +2ms
  prisma:client  dirname /prisma-14632/node_modules/.prisma/client +0ms
  prisma:client  relativePath ../../../prisma +0ms
  prisma:client  cwd /prisma-14632/prisma +0ms
  prisma:client  clientVersion 4.3.1 +1ms
  prisma:client  clientEngineType library +0ms
  prisma:client:libraryEngine  internalSetup +0ms
  prisma:client:libraryEngine:loader  Searching for Query Engine Library in /prisma-14632/node_modules/.prisma/client +0ms
  prisma:client:libraryEngine:loader  loadEngine using /prisma-14632/node_modules/.prisma/client/libquery_engine-debian-openssl-1.1.x.so.node +1ms
  prisma:client  Prisma Client call: +49ms
  prisma:client  prisma.labels.findUnique({
  where: {
    id: '11111111111111111111'
  }
}) +1ms
  prisma:client  Generated request: +0ms
  prisma:client  query {
  findUniquelabels(where: {
    id: 11111111111111111111
  }) {
    id
  }
}
 +0ms
  prisma:client:libraryEngine  sending request, this.libraryStarted: false +51ms
  prisma:client:libraryEngine  library starting +0ms
  prisma:client:libraryEngine  library started +2ms
  prisma:client:request_handler  PrismaClientKnownRequestError: Query parsing failure: A number used in the query does not fit into a 64 bit signed integer. Consider using `BigInt` as field type if you're trying to store large integers.
    at prismaGraphQLToJSError (/prisma-14632/node_modules/@prisma/client/runtime/index.js:20426:12)
    at LibraryEngine.buildQueryError (/prisma-14632/node_modules/@prisma/client/runtime/index.js:26363:12)
    at LibraryEngine.request (/prisma-14632/node_modules/@prisma/client/runtime/index.js:26297:22) {
  code: 'P2033',
  clientVersion: '4.3.1',
  meta: {
    details: "Query parsing failure: A number used in the query does not fit into a 64 bit signed integer. Consider using `BigInt` as field type if you're trying to store large integers."
  }
} +0ms
PrismaClientKnownRequestError: 
Invalid `prisma.labels.findUnique()` invocation in
/prisma-14632/script.ts:6:35

  3 const prisma = new PrismaClient()
  4 
  5 async function main() {
→ 6   const rec = await prisma.labels.findUnique(
Query parsing failure: A number used in the query does not fit into a 64 bit signed integer. Consider using `BigInt` as field type if you're trying to store large integers.
    at RequestHandler.handleRequestError (/prisma-14632/node_modules/@prisma/client/runtime/index.js:29909:13)
    at RequestHandler.request (/prisma-14632/node_modules/@prisma/client/runtime/index.js:29892:12)
    at async PrismaClient._request (/prisma-14632/node_modules/@prisma/client/runtime/index.js:30864:16)
    at async main (/prisma-14632/script.ts:6:15) {
  code: 'P2033',
  clientVersion: '4.3.1',
  meta: {
    details: "Query parsing failure: A number used in the query does not fit into a 64 bit signed integer. Consider using `BigInt` as field type if you're trying to store large integers."
  }
}
  prisma:client:libraryEngine  library stopping +23ms
  prisma:client:libraryEngine  library stopped +0ms
  prisma:client:libraryEngine:exitHooks  exit event received: exit +0ms

We should extend our support for unsigned integers. There's some good preliminary work related to UUIDs by @tomhoule, that should help here too.

[marcelomorais]

the same happens for sqlite.

Did anyone found a workaround for that?

[miguelangelrm]

This is still an issue as of Prisma 4.11

[anotheri]

Any updates on this one?

[Druue]

Hey folks!

I can confirm with the following code

const prisma = new PrismaClient({
  log: ["query"],
});

await prisma.labels.create({
    data: {
      id: BigInt(11111111111111111111),
    },
  });

the following error on v4.1.0 of Prisma

> ts-node index.ts

PrismaClientKnownRequestError: 
Invalid `prisma.labels.create()` invocation in
/src/prisma_debug/index.ts:16:23

  13 // prisma.$use
  14 
  15 const populate = async () => {
→ 16   await prisma.labels.create(
  Query parsing failure: A number used in the query does not fit into a 64 bit signed integer. Consider using `BigInt` as field type if you're trying to store large integers.
    at RequestHandler.handleRequestError (/Users/soph/src/prisma_debug/node_modules/@prisma/client/runtime/index.js:28658:13)
    at RequestHandler.request (/Users/soph/src/prisma_debug/node_modules/@prisma/client/runtime/index.js:28640:12)
    at async consumer (/Users/soph/src/prisma_debug/node_modules/@prisma/client/runtime/index.js:29618:18)
    at async PrismaClient._request (/Users/soph/src/prisma_debug/node_modules/@prisma/client/runtime/index.js:29639:16) {
  code: 'P2033',
  clientVersion: '4.1.0',
  meta: {
    details: "Query parsing failure: A number used in the query does not fit into a 64 bit signed integer. Consider using `BigInt` as field type if you're trying to store large integers."
  }
}

However, when updating to latest (now 5.11.0), I can no longer reproduce

> ts-node index.ts

PrismaClientUnknownRequestError: 
Invalid `prisma.labels.create()` invocation in
/Users/soph/src/prisma_debug/index.ts:16:23

  13 // prisma.$use
  14 
  15 const populate = async () => {
→ 16   await prisma.labels.create(
Could not convert argument value Object {"$type": String("BigInt"), "value": String("11111111111111110656")} to ArgumentValue.
    at In.handleRequestError (/Users/soph/src/prisma_debug/node_modules/@prisma/client/runtime/library.js:122:7044)
    at In.handleAndLogRequestError (/Users/soph/src/prisma_debug/node_modules/@prisma/client/runtime/library.js:122:6188)
    at In.request (/Users/soph/src/prisma_debug/node_modules/@prisma/client/runtime/library.js:122:5896)
    at async l (/Users/soph/src/prisma_debug/node_modules/@prisma/client/runtime/library.js:127:10871) {
  clientVersion: '5.11.0'
}

This change in messaging happened in Prisma v5.0.0, i.e. the first error occurs up-to and including v4.14 and then the second error from v5.0.0. This coincides with the transition to the JSON Protocol - the source for the previous error can be found here

Using the JsonProtocol preview feature on 4.14 further highlights the change in messaging is due to that switch; the underlying bug still exists.

I have a reproduction here