Calling findUnique concurrently with different key order causes one of them to return null

[BlooJeans]

Bug description

There is an issue with Prisma which causes concurrent usages of findUnique() to return null values if the calls specify the uniqueWhereInput keys in different orders

• Only happens with findUnique or findUniqueOrThrow
• Only when multiple findUnique calls are made concurrently and Prisma runs them in a batched query.
• The uniqueWhereInput needs to be different for the problem to occur

Example:

  const funcA = () =>
    prisma.relationship.findUnique({
      where: {
        fooId_barId: {
          // note the order of these two keys
          fooId,
          barId,
        },
      },
    });

  const funcB = () =>
    prisma.relationship.findUnique({
      where: {
        fooId_barId: {
          // note the order of these two keys
          barId,
          fooId,
        },
      },
    });

  const test1 = await Promise.all([funcA(), funcA()]);
  // test1[0]: found
  // test1[1]: found

  const test2 = await Promise.all([funcB(), funcB()]);
  // test2[0]: found
  // test2[1]: found

  const test3 = await Promise.all([funcA(), funcB()]);
  // test3[0]: found
  // test3[1]: null (!!)

  const test4 = await Promise.all([funcB(), funcA()]);
  // test4[0]: found
  // test4[1]: null (!!)

Workraround

• Be consistent with all usages of findUnique
• Switch one of the concurrent calls to use findFirst instead

How to reproduce

I created a standalone repo at https://github.com/BlooJeans/prisma-issue-concurrent-findUnique-keyorder-matters
Thanks to @stayradiated for providing an easy to use barebones repo to base off of

Expected behavior

Two concurrent calls to findUnique() shouldn't interfere with eachother, specifically when they're called with two different orders.

Prisma information

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

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

model Foo {
  id           String            @id @default(uuid())
  Relationship Relationship[]
}

model Bar {
  id           String            @id @default(uuid())
  Relationship Relationship[]
}

model Relationship {
  id String @id @default(uuid())

  fooId String
  foo   Foo @relation(fields: [fooId], references: [id])

  barId String
  bar   Bar @relation(fields: [barId], references: [id])

  @@unique([fooId, barId])
}

  await prisma.relationship.deleteMany();
  await prisma.foo.deleteMany();
  await prisma.bar.deleteMany();

  await prisma.foo.createMany({
    data: [{ id: "1" }],
  });

  await prisma.bar.createMany({
    data: [{ id: "5" }],
  });

  await prisma.relationship.createMany({
    data: [
      { fooId: "1", barId: "5" },
    ],
  });

  const minimalExample = await Promise.all([
    prisma.relationship.findUnique({
      where: {
        fooId_barId: {
          // note the order of these two keys
          fooId,
          barId,
        },
      },
    }),
    prisma.relationship.findUnique({
      where: {
        fooId_barId: {
          // note the order of these two keys
          barId,
          fooId,
        },
      },
    }),
  ]);
  console.log("minimalExample", minimalExample);

Which produces:

minimalExample [
  {
    id: '0788ef75-8e7d-4759-b141-b254ebe2142d',
    fooId: '1',
    barId: '5'
  },
  null
]

Same query but with DEBUG="*"

  prisma:client  prisma.relationship.findUnique({
  where: {
    fooId_barId: {
      fooId: '1',
      barId: '5'
    }
  }
}) +0ms
  prisma:client  Generated request: +0ms
  prisma:client  query {
  findUniqueRelationship(where: {
    fooId_barId: {
      fooId: "1"
      barId: "5"
    }
  }) {
    id
    fooId
    barId
  }
}
 +0ms
  prisma:client  Prisma Client call: +0ms
  prisma:client  prisma.relationship.findUnique({
  where: {
    fooId_barId: {
      barId: '5',
      fooId: '1'
    }
  }
}) +0ms
  prisma:client  Generated request: +0ms
  prisma:client  query {
  findUniqueRelationship(where: {
    fooId_barId: {
      barId: "5"
      fooId: "1"
    }
  }) {
    id
    fooId
    barId
  }
}
 +0ms
  prisma:client:libraryEngine  requestBatch +4ms
minimalExample [
  {
    id: 'dc61a566-23e0-4f98-a452-e4030d293ea0',
    fooId: '1',
    barId: '5'
  },
  null
]

Environment & setup

• OS: Ubuntu
• Database: Postgres
• Node.js version: v16.17.0

Prisma Version

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

[Weakky]

Hey @BlooJeans,

I'm confirming we can reproduce this bug, thanks for the report 🙏🏻