Prisma client crashes when counting m-n self-relation

[nichitaS40]

Bug description

Running a basic findUnique which includes

 _count: {
        select: {
          posts: true,
          followers: true
        }
}

Crashes prisma with this error:

The user model contains this:

  followers User[] @relation(name: "approved-followers", references: [id])
  following User[] @relation(name: "approved-followers", references: [id])

If i remove the followers: true variable, and leave only the posts count, that works correctly.

How to reproduce

Create a model, add an m-n self relation to it, then run a query which includes the count of one of those relations

Expected behavior

It should either return the count, or throw a nice error message. At the moment it crashes

Prisma information

model User {
  id              Int       @id @default(autoincrement())
  ...
  followers User[] @relation(name: "approved-followers", references: [id])
  following User[] @relation(name: "approved-followers", references: [id])
   ...
}

Environment & setup

• OS:
• Database:
• Node.js version:

MacOS, PostgreSQL database

Node is: v16.3.0

Prisma Version

prisma               : 2.23.0
@prisma/client       : 2.23.0
Current platform     : darwin
Query Engine         : query-engine adf5e8cba3daf12d456d911d72b6e9418681b28b (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine     : migration-engine-cli adf5e8cba3daf12d456d911d72b6e9418681b28b (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core adf5e8cba3daf12d456d911d72b6e9418681b28b (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary        : prisma-fmt adf5e8cba3daf12d456d911d72b6e9418681b28b (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash : adf5e8cba3daf12d456d911d72b6e9418681b28b
Studio               : 0.393.0
Preview Features     : selectRelationCount

[nichitaS40]

After some more debugging, this is the query that prisma generates:

SELECT "public"."User"."id", "public"."User"."email", "public"."User"."username", "public"."User"."password", "public"."User"."firstName", "public"."User"."lastName", "public"."User"."age", "public"."User"."emailVerified", "public"."User"."approved", "public"."User"."profilePicture", "public"."User"."description", "public"."User"."refreshToken", "aggr_selection_0_User"."_aggr_count_followers" FROM "public"."User" LEFT JOIN (SELECT "public"."User"."id", COUNT(*) AS "_aggr_count_followers" FROM "public"."_approved-followers" INNER JOIN "public"."User" ON ("public"."User"."id" = ("public"."_approved-followers"."A")) INNER JOIN "public"."User" ON ("public"."User"."id" = ("public"."_approved-followers"."B")) GROUP BY "public"."User"."id") AS "aggr_selection_0_User" ON ("public"."User"."id" = "aggr_selection_0_User"."id") WHERE "public"."User"."id" = $1 LIMIT $2 OFFSET $3

Looks like the problem is the two inner joins, only one of those really makes sense (though i'm not sure which)

[nichitaS40]

After even more debugging, it looks like the issue is in this line:

https://github.com/prisma/prisma-engines/blob/master/query-engine/connectors/sql-query-connector/src/join_utils.rs#L123

Here I am stuck however :( I don't understand why it would do 2 inner joins (since it would seem like the first one is enough to count relations). But you guys are the experts, hopefully this shortens the tracking down time a bit!

Edit: And indeed, running the query with the second inner join removed (for a different m-n query) yields the same result as the prisma version.. perhaps there's a special case I'm not considering where the second one is required?

[pantharshit00]

I can confirm this bug. We will have a proper look here.

I posted a minimal reproduction for this here: https://github.com/harshit-test-org/prisma-issue-7807