findMany broken with many relations to same entity

[zackpollard]

Bug description

When making a findMany query to an entity (A) that has a ManyToMany relation to another entity (B), if B is referred to in many of the returned A entities, only one of the A entities actually has B returned.

How to reproduce

1. Run a findMany query with a Many to Many relationship
2. Have many of the base entities reference the same related entity
3. Only one of the base entities will have the related entity in the response

Expected behavior

All related entities should be returned correctly in the prisma mapping.

Prisma information

Schema

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

generator client {
  provider = "prisma-client-js"
  output   = "../node_modules/@prisma/client/generated"
}

model Group {
  groupId   BigInt   @id
  users     User[]
  tags      Tag[]
  broadcasts Boolean @default(true)
  adminOnly Boolean @default(false)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model User {
  userId     BigInt     @id
  groups     Group[]
  tags       Tag[]
  username   String?
  firstName  String?
  lastName   String?
  lastSeen   DateTime   @default(now())
  createdAt  DateTime   @default(now())
  updatedAt  DateTime   @updatedAt
}

model Tag {
  tagId      String     @id @default(uuid())
  name       String
  group      Group      @relation(fields: [groupId], references: [groupId])
  groupId    BigInt
  users      User[]
  lastUsed   DateTime?
  createdAt  DateTime   @default(now())
  updatedAt  DateTime   @updatedAt

  @@unique([name, groupId])
}

Query

const tags = await prisma.tag.findMany({
  where: { groupId },
  include: { users: true }
});

Environment & setup

• OS: Windows and Linux
• Database: PostgreSQL
• Node.js version: v14.17.5

Prisma Version

$ prisma -v                                                       
Environment variables loaded from .env
prisma                  : 3.10.0
@prisma/client          : 3.10.0
Current platform        : windows
Query Engine (Node-API) : libquery-engine 73e60b76d394f8d37d8ebd1f8918c79029f0db86 (at node_modules\@prisma\e
ngines\query_engine-windows.dll.node)
Migration Engine        : migration-engine-cli 73e60b76d394f8d37d8ebd1f8918c79029f0db86 (at node_modules\@pri
sma\engines\migration-engine-windows.exe)
Introspection Engine    : introspection-core 73e60b76d394f8d37d8ebd1f8918c79029f0db86 (at node_modules\@prism
a\engines\introspection-engine-windows.exe)
Format Binary           : prisma-fmt 73e60b76d394f8d37d8ebd1f8918c79029f0db86 (at node_modules\@prisma\engine
s\prisma-fmt-windows.exe)
Default Engines Hash    : 73e60b76d394f8d37d8ebd1f8918c79029f0db86
Studio                  : 0.458.0

Example:

This query:

const tag = await prisma.tag.findUnique({
  select: { users: true, name: true },
  where: { name_groupId: { groupId, name } },
  rejectOnNotFound: false
});

returns the following for the respective args:

(groupid: A, name: 'test')
{
  users: [
    {
      userId: 87425504n,
      username: 'zackpollard',
      firstName: 'Zack',
      lastName: 'Pollard',
      lastSeen: 2022-03-09T00:43:48.688Z,
      createdAt: 2022-02-28T02:42:03.063Z,
      updatedAt: 2022-03-09T00:43:48.721Z
    }
  ],
  name: 'test'
}

(groupId: A, name: 'test2')
{
  users: [
    {
      userId: 87425504n,
      username: 'zackpollard',
      firstName: 'Zack',
      lastName: 'Pollard',
      lastSeen: 2022-03-09T00:44:49.338Z,
      createdAt: 2022-02-28T02:42:03.063Z,
      updatedAt: 2022-03-09T00:44:49.340Z
    }
  ],
  name: 'test2'
}

The findMany query for the same group:

const tags = await prisma.tag.findMany({
  where: { groupId },
  include: { users: true }
});

Returns:

(groupId: A)
[
  {
    tagId: 'b573fab0-1043-480c-a1c1-51edd1eff4c3',
    name: 'test',
    groupId: -747796799n,
    createdAt: 2022-02-28T02:42:03.296Z,
    updatedAt: 2022-02-28T02:42:03.296Z,
    users: []
  },
  {
    tagId: 'f2357c37-a468-461a-bc15-d05f010eb787',
    name: 'test2',
    groupId: -747796799n,
    lastUsed: null,
    createdAt: 2022-03-09T00:20:00.584Z,
    updatedAt: 2022-03-09T00:20:00.585Z,
    users: [ [Object] ]
  }
]

As you can see, the first tag "test" is missing the user that "test2" shares with it

[zackpollard]

I hope you can just tell me that I'm doing something wrong because the alternative where prisma isn't resolving these relations correctly would be quite a substantial bug. 🤞

[zackpollard]

On further investigation, it is an issue with the BigInt type specifically as the primary key between the two entities. It looks like prisma swapped from BigInt to bigint in version 2.22.0 and it was broken until 2.24.0. BigInt works correctly as of 2.22.0 but when it is fixed in 2.24.0, this bug exists from that version in every version until 3.10.0. I saw this commit in 2.24.0 but doesn't seem that suspect. https://github.com/prisma/prisma-engines/pull/1975/files
I imagine the actual problem was from the original switch from BigInt to bigint type.

[JingLi1998]

I've encountered the same problem too. I feel like Prisma is slightly lacking in support for bigint types. I guess it makes sense because bigint isn't widely used in the Javascript ecosystem as opposed to number.

That being said, bigint is quite important because we shouldn't be coerced into using int just because Prisma has better support for it. Converting int to bigint if needed down the line is an inconvenient migration process.

I had a look through the JS/TS codebase and couldn't find anything that hinted a bug in the code. Perhaps there is something else going on in the Rust engine itself. I've tracked down the many-to-many nested read implemention to this particular file: https://github.com/prisma/prisma-engines/blob/main/query-engine/core/src/interpreter/query_interpreters/nested_read.rs#L10

Unfortunately I'm not familiar with Rust so I'm not sure how to replicate the bug and fix it. Feel free to ping me and I'm happy to work on it together

[Weakky]

Hey folks,

Either I'm doing something wrong or there's something going on with your data. Either way, I unfortunately can't reproduce your issue.

Would you mind setting up a small reproduction with seed data?

Thank you 🙏

[zackpollard]

Hey folks,

Either I'm doing something wrong or there's something going on with your data. Either way, I unfortunately can't reproduce your issue.

Would you mind setting up a small reproduction with seed data?

Thank you 🙏

Hey Weakky,

It's been a while since i've worked on this code. However the issue exists when two entities (tags) return a nested array of entities (users) that share one of the related entities, i.e. both have the same user connected to the tag. In that case, only one of the tags shows the user whereas both should show it. In your data set if you add Flavian to tag_b or Zack to tag_a you should see the problem.

Cheers!

[dagistan-tuncbilek]

Hey folks,

Either I'm doing something wrong or there's something going on with your data. Either way, I unfortunately can't reproduce your issue.

Would you mind setting up a small reproduction with seed data?

Thank you pray

Hi Weakky,
I think this error occurs when we have too many in same entity as nested.
I tested it in companies and customers tables. companies have more than 1000 customers and i tried to get customers with companies.
"customer.findMany({ include: { companies: { select: { company: true } } }} })"
According to my tests, If 200 and less customers have same company, there is no error. But if there are more then 1000 customer in a company, company returns null or companies are empty array.
Cheers

EDIT: Maybe problem is related to third sql. For example; there should be unique ids in companies table query. But i think, prisma adds all ids one by one. For ex: 1000 customer with same company id, in sql statement there are 1000 same id as parameter. It should be 1 id.

EDIT-2: I found another issue related to this one. I tried to fetch data in an order. But findMany function with "where in" query doesnt work with more than 1000 records too. Same result, empty array.

EDIT-3: I am not using BIGINT, my ids are all int.

[Weakky]

Hey @dagistan-tuncbilek,

It would be very helpful if you could open another issue and provide a reproduction (that includes seed data). That'd make it very easy for us to fix your issue.

Thank you 🙏

[dagistan-tuncbilek]

Hey @dagistan-tuncbilek,

It would be very helpful if you could open another issue and provide a reproduction (that includes seed data). That'd make it very easy for us to fix your issue.

Thank you pray

Please see the issue #14019
I tried to add db script file for testing.

[Weakky]

Hey,

This issue was just fixed and will be available in the next release (4.0).

Thanks for reporting 🙏

[Weakky]

Please see the issue #14019
I tried to add db script file for testing.

Thank you @dagistan-tuncbilek, we'll have a look 🙏

[zackpollard]

Excellent, thankyou for resolving it @Weakky