Too many records are returned in 4.11.0 when using nested include and @unique field is null

[johanforslund]

Bug description

We have noticed a bug in Prisma since upgrading from 4.10.1 to 4.11.0. In our database we have a top-down relation between three tables (A->B->C) where the middle table (B) includes an optional @unique string. When this field is null, it seems to cause issues when Prisma joins the tables on findUnique since we get duplicate data from the bottom table (C).

The bug disappears if I move the @unique constraint from the line where the optional string is declared, and instead place it under my other @@unique constraints.

How to reproduce

Call findUnique and include two other tables where the second table includes an optional @unique constraint. The problem happens when this optional field is null.

Expected behavior

No response

Prisma information

The schema looks something like:

model A {
  a1    String
  a2    String
  bs    B[]

  @@unique([a1, a2])
}

model B {
  b1    String
  b2    String
  b3    String?   @unique
  a     A         @relation(fields: [a1, a2], references: [a1, a2])
  a1    String
  a2    String
  cs    C[]

  @@unique([b1, b2])
}

model C {
  c   String    @unique
  b    B         @relation(fields: [b1, b2], references: [b1, b2])
  b1   String
  b2   String
}

I query the data as

await prisma.a.findUnique({
      where: { a1_a2: { a1: 'a1', a2: 'a2', } },
      include: { b: { include: { c: true } } },
    });

This gives me data looking like:

{
    a1: 'a1',
    a2: 'a2',
    bs: [
      {
        b1: 'b11',
        b2: 'b21',
        b3: null,
        a1: 'a1',
        a2: 'a2',
        cs: [
          {
            c: 'c1',
            b1: 'b11',
            b2: 'b21',
          },
          {
            c: 'c2',
            b1: 'b12',
            b2: 'b22',
          },
        ],
      },
      {
        b1: 'b12',
        b2: 'b22',
        b3: null,
        a1: 'a1',
        a2: 'a2',
        cs: [
          {
            c: 'c1',
            b1: 'b11',
            b2: 'b21',
          },
          {
            c: 'c2',
            b1: 'b12',
            b2: 'b22',
          },
        ],
      },
    ],
  }

I only expect one element in each of the cs arrays.

Again, the problem is solved if I change model B to

model B {
  b1    String
  b2    String
  b3    String?
  a     A         @relation(fields: [a1, a2], references: [a1, a2])
  a1    String
  a2    String
  cs    C[]

  @@unique([b1, b2])
  @@unique([b3])
}

which makes me think that the ordering of the unique constraints matter internally in Prisma. Because it does not work if I do

  @@unique([b3])
  @@unique([b1, b2])

Environment & setup

• OS: macOS
• Database: PostgresQL
• Node.js version: 18.12.1

Prisma Version

4.11.0

[aqrln]

Thanks for the report @johanforslund, I can reproduce and confirm this.

FWIW, I couldn't even recreate your db structure correctly with the latest Prisma since c2 was being connected to the wrong B, so I had to use Prisma 4.10.1 to seed the data — not sure if it has the same root cause or if it's a separate bug.

I'll bisect the commit where this starts happening and tag the appropriate people.

[aqrln]

git bisect points at prisma/prisma-engines#3748, cc @tomhoule

[tomhoule]

@Weakky could it be the prisma/prisma-engines#3762 problem?

[Weakky]

@tom It does look very similar indeed!

[aqrln]

FWIW, I couldn't even recreate your db structure correctly with the latest Prisma since c2 was being connected to the wrong B, so I had to use Prisma 4.10.1 to seed the data — not sure if it has the same root cause or if it's a separate bug.

I realized I had only posted about it in internal slack but not here, but yes, git bisect points at the same commit for that problem too.

As a specific example of a query that writes wrong data to the database:

  await prisma.b.update({
    where: {
      b1_b2: { b1: 'b11', b2: 'b21' },
    },
    data: {
      cs: {
        create: {
          c: 'c1',
        },
      },
    },
  })

[aqrln]

@Weakky @tomhoule prisma/prisma-engines#3762 indeed fixes both issues, I just tested it.