Calling `findUnique` concurrently with a `DateTime` column causes it to return `null`

[stayradiated]

Bug description

Seems to be the same issue as in https://github.com/prisma/prisma/issues/4438 and https://github.com/prisma/prisma/issues/5941.

There is an issue with Prisma which causes findUnique() to return null values, even though the row definitely exists.

• Only happens with findUnique
• Only when multiple findUnique calls are made concurrently and Prisma runs them in a batched query.
• Only seems to happen when a DateTime column is used.

How to reproduce

I have created a standalone repository with reproduction code available at: https://github.com/stayradiated/prisma-issue-findUnique-returns-null

Expected behavior

The findUnique method should have the same behaviour whether run by itself or concurrently.

Prisma information

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

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model ModelWithUniqueDate {
  id   Int      @id @default(autoincrement())
  date DateTime @unique @db.Date
}

const prisma = new PrismaClient();

await prisma.modelWithUniqueDate.createMany({
  data: [
    { date: "2011-01-01T00:00:00Z" },
    { date: "2022-02-02T00:00:00Z" }
  ],
});

const rowList = await Promise.all([
  prisma.modelWithUniqueDate.findUnique({
    where: { date: "2011-01-01T00:00:00Z" },
  }),
  prisma.modelWithUniqueDate.findUnique({
    where: { date: "2022-02-02T00:00:00Z" },
  })
]);

console.log(rowList) // prints [null, null]

Environment & setup

• OS: Ubuntu (also reproduced on macOS)
• Database: PostgreSQL
• Node.js version: v16.17.0

Prisma Version

Environment variables loaded from .env
prisma                  : 4.2.1
@prisma/client          : 4.2.1
Current platform        : debian-openssl-1.1.x
Query Engine (Node-API) : libquery-engine 2920a97877e12e055c1333079b8d19cee7f33826 (at node_modules/@prisma/engines/libquery_engine-debian-openssl-1.1.x.so.node)
Migration Engine        : migration-engine-cli 2920a97877e12e055c1333079b8d19cee7f33826 (at node_modules/@prisma/engines/migration-engine-debian-openssl-1.1.x)
Introspection Engine    : introspection-core 2920a97877e12e055c1333079b8d19cee7f33826 (at node_modules/@prisma/engines/introspection-engine-debian-openssl-1.1.x)
Format Binary           : prisma-fmt 2920a97877e12e055c1333079b8d19cee7f33826 (at node_modules/@prisma/engines/prisma-fmt-debian-openssl-1.1.x)
Default Engines Hash    : 2920a97877e12e055c1333079b8d19cee7f33826
Studio                  : 0.469.0

[danstarns]

Can confirm, thanks for the reproduction steps.

Workaround:

Use new Date()

const date1 = new Date('2011-01-01T00:00:00Z')
const date2 = new Date('2022-02-02T00:00:00Z')

await prisma.modelWithUniqueDate.createMany({
  data: [{ date: date1 }, { date: date2 }],
})

const rowList = await Promise.all([
  prisma.modelWithUniqueDate.findUnique({
    where: { date: date1 },
  }),
  prisma.modelWithUniqueDate.findUnique({
    where: { date: date2 },
  }),
])

console.log(rowList)
// prints [
//   { id: 1, date: 2011-01-01T00:00:00.000Z },
//   { id: 2, date: 2022-02-02T00:00:00.000Z }
// ]

[mhammerc]

Hello !

I'm on Prisma 4.2.1 with PostgreSQL. Tested with same behaviors back to 3.14.0.

It appears I have the same bug with a composite @@id([fromId, toId]), both of type String.

Case 1 with both tuples in the same order [ctx.user.id, user.id]:

    const batch = await Promise.all([
      ctx.prisma.friendship.findUnique({
        where: {fromId_toId: {fromId: ctx.user.id, toId: user.id}},
      }),
      ctx.prisma.friendship.findUnique({
        where: {fromId_toId: {toId: ctx.user.id, fromId: user.id}},
      }),
    ]);

    console.log(batch); // [null, null]

    const fromMe = await ctx.prisma.friendship.findUnique({
      where: {fromId_toId: {fromId: ctx.user.id, toId: user.id}},
    });
    const toMe = await ctx.prisma.friendship.findUnique({
      where: {fromId_toId: {toId: ctx.user.id, fromId: user.id}},
    });

    console.log(fromMe, toMe); // [null, VALUE] as expected

Get [null, null] (wrong).

Case 2 same as case 1, but second query become first query:

const batch = await Promise.all([
  ctx.prisma.friendship.findUnique({
    where: {fromId_toId: {toId: ctx.user.id, fromId: user.id}}, // This was the second query previously
  }),
  ctx.prisma.friendship.findUnique({
    where: {fromId_toId: {fromId: ctx.user.id, toId: user.id}}, // This was the first query previously
  }),
]);

console.log(batch); // [VALUE, VALUE]

Get [VALUE, VALUE] (wrong) (opposite result than case 1).

Case 3, both tuples are different. First is [ctx.user.id, user.id] and second is [user.id, ctx.user.id].

const batch = await Promise.all([
  ctx.prisma.friendship.findUnique({
    where: {fromId_toId: {fromId: ctx.user.id, toId: user.id}},
  }),
  ctx.prisma.friendship.findUnique({
    where: {fromId_toId: {fromId: user.id, toId: ctx.user.id}},
  }),
]);

console.log(batch); // [null, VALUE]

Get [null, VALUE] (right).

My guess is prisma works with tuples of value to batch and differentiate different findUniques.

[stayradiated]

Can confirm, thanks for the reproduction steps.

Workaround:

Use new Date()

Thank you for the suggestion @danstarns, I can confirm that works for us :+1:

[millsp]

Internal document