Prisma crashes when I use the same table for counting and findUnique

[talentlessguy]

Bug description

When I try to count followers of a User model, which itself is an array of users, like this:

 const users = await ctx.prisma.user.findUnique({
  where: { id: source.id },
  include: {
    _count: {
      select: {
        followers: true
      }
    }
  }
})

I get this error:

ApolloError: 
Invalid `prisma.user.findUnique()` invocation:


  Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("42712"), message: "table name \"users\" specified more than once", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("parse_relation.c"), line: Some(442), routine: Some("checkNameSpaceConflicts") }) }) })
    at new ApolloError (/tmp/prisma-count-repro/node_modules/.pnpm/@apollo+client@3.3.20_graphql@15.5.1+react@17.0.2/node_modules/@apollo/client/errors/errors.cjs.js:31:28)
    at /tmp/prisma-count-repro/node_modules/.pnpm/@apollo+client@3.3.20_graphql@15.5.1+react@17.0.2/node_modules/@apollo/client/core/core.cjs.js:1474:47
    at both (/tmp/prisma-count-repro/node_modules/.pnpm/@apollo+client@3.3.20_graphql@15.5.1+react@17.0.2/node_modules/@apollo/client/utilities/utilities.cjs.js:963:53)
    at /tmp/prisma-count-repro/node_modules/.pnpm/@apollo+client@3.3.20_graphql@15.5.1+react@17.0.2/node_modules/@apollo/client/utilities/utilities.cjs.js:956:72
    at new Promise (<anonymous>)
    at Object.then (/tmp/prisma-count-repro/node_modules/.pnpm/@apollo+client@3.3.20_graphql@15.5.1+react@17.0.2/node_modules/@apollo/client/utilities/utilities.cjs.js:956:24)
    at Object.next (/tmp/prisma-count-repro/node_modules/.pnpm/@apollo+client@3.3.20_graphql@15.5.1+react@17.0.2/node_modules/@apollo/client/utilities/utilities.cjs.js:964:49)
    at notifySubscription (/tmp/prisma-count-repro/node_modules/.pnpm/zen-observable@0.8.15/node_modules/zen-observable/lib/Observable.js:135:18)
    at onNotify (/tmp/prisma-count-repro/node_modules/.pnpm/zen-observable@0.8.15/node_modules/zen-observable/lib/Observable.js:179:3)
    at SubscriptionObserver.next (/tmp/prisma-count-repro/node_modules/.pnpm/zen-observable@0.8.15/node_modules/zen-observable/lib/Observable.js:235:7) {
  graphQLErrors: [
    Error: 
    Invalid `prisma.user.findUnique()` invocation:
    
    
      Error occurred during query execution:
    ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("42712"), message: "table name \"users\" specified more than once", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("parse_relation.c"), line: Some(442), routine: Some("checkNameSpaceConflicts") }) }) })
        at cb (/tmp/prisma-count-repro/node_modules/.pnpm/@prisma+client@2.23.0_prisma@2.23.0/node_modules/@prisma/client/runtime/index.js:35105:17)
        at runMicrotasks (<anonymous>)
        at processTicksAndRejections (node:internal/process/task_queues:96:5)
        at async resolve (webpack-internal:///./graphql/resolvers.ts:47:23)
        at async Promise.all (index 1)
        at async Promise.all (index 0) {
      locations: [],
      path: [Array]
    }
  ],
  networkError: null,
  extraInfo: undefined
}

How to reproduce

1. Clone https://github.com/talentlessguy/prisma-count-repro
2. Add these to .env:

BASE_URL=http://localhost:3000
NEXTAUTH_URL=http://localhost:3000

3. Go to github.com > Settings > Developer > OAuth Apps and save GITHUB_ID and GITHUB_SECRET to an .env file

4. Run

# db startup
pnpm generate
pnpm dev

5. Open /api/auth/signin. Sign In with GitHub.
6. Go to /your-github-nickname. Nickname is a slug so spaces are converted to dashes.
7. See this error

Expected behavior

I would expect it to not crash and return the followers count

Prisma information

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["selectRelationCount"]
}

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

model Account {
  id                 Int       @id @default(autoincrement())
  compoundId         String    @unique @map("compound_id")
  userId             Int       @map("user_id")
  providerType       String    @map("provider_type")
  providerId         String    @map("provider_id")
  providerAccountId  String    @map("provider_account_id")
  refreshToken       String?   @map("refresh_token")
  accessToken        String?   @map("access_token")
  accessTokenExpires DateTime? @map("access_token_expires")
  createdAt          DateTime  @default(now()) @map("created_at")
  updatedAt          DateTime  @default(now()) @map("updated_at")

  @@index([providerAccountId], name: "providerAccountId")
  @@index([providerId], name: "providerId")
  @@index([userId], name: "userId")
  @@map("accounts")
}

model Session {
  id           Int      @id @default(autoincrement())
  userId       Int      @map("user_id")
  expires      DateTime
  sessionToken String   @unique @map("session_token")
  accessToken  String   @unique @map("access_token")
  createdAt    DateTime @default(now()) @map("created_at")
  updatedAt    DateTime @default(now()) @map("updated_at")

  @@map("sessions")
}

model User {
  id            Int       @id @default(autoincrement())
  name          String
  email         String?   @unique
  emailVerified DateTime? @map("email_verified")
  image         String?
  createdAt     DateTime  @default(now()) @map("created_at")
  updatedAt     DateTime  @default(now()) @map("updated_at")
  posts         Post[]
  views         Int       @default(0)
  slug          String    @unique @default(uuid())

  following User[] @relation(name: "UserFollows", references: [id])
  followers User[] @relation(name: "UserFollows", references: [id])
  userId    Int?
  @@map("users")
}

model VerificationRequest {
  id         Int      @id @default(autoincrement())
  identifier String
  token      String   @unique
  expires    DateTime
  createdAt  DateTime @default(now()) @map("created_at")
  updatedAt  DateTime @default(now()) @map("updated_at")

  @@map("verification_requests")
}

model Image {
  id               Int      @id @default(autoincrement())
  publicId         String   @unique @map("public_id")
  version          Int
  signature        String
  width            Int
  height           Int
  format           String
  resourceType     String   @map("resource_type")
  createdAt        DateTime @map("created_at")
  bytes            Int
  type             String
  etag             String
  url              String   @unique
  secureUrl        String   @unique @map("secure_url")
  originalFilename String   @map("original_filename")

  Post Post[]

  @@map("image")
}

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now()) @map("created_at")
  caption   String?
  image     Image    @relation(fields: [imagePublicId], references: [publicId])
  published Boolean  @default(false)
  authorId  Int      @map("author_id")
  author    User     @relation(fields: [authorId], references: [id])
  views     Int      @default(0)

  imagePublicId String
  @@map("post")
}

Environment & setup

• OS: Manjaro
• Database: PostgreSQL 13.3
• Node.js version: 16.4

Prisma Version

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

[pantharshit00]

Thanks for reporting this, I have isolated the reproduction in a small script which can use in further debugging. I can confirm this issue.

Minimal reproduction: https://github.com/harshit-test-org/prisma-issue-7893