Queries that include the count of a relation that has multiple foreign keys translates to incorrect SQL

[frissonlabs]

Bug description

Running a query to retrieve the count of a relation that has multiple foreign keys results in the following error:

Raw query failed. Code: `42804`. Message: `db error: ERROR: argument of JOIN/ON must be type boolean, not type record`

This appears to happen because the ON clause for the JOIN uses comma-separated boolean expressions instead of AND-seperated boolean expressions.

For example, this doesn't work:

SELECT * FROM "public"."User" LEFT JOIN (SELECT "public"."User".* FROM "public"."User") AS "user2" ON ("public"."User"."id" = "user2"."id","public"."User"."id" 
= "user2"."id")`

But this does:

SELECT * FROM "public"."User" LEFT JOIN (SELECT "public"."User".* FROM "public"."User") AS "user2" ON ("public"."User"."id" = "user2"."id" AND "public"."User"."id" 
= "user2"."id")`

I'd suggest that such queries use AND instead of commas during the transformation into raw SQL.

How to reproduce

Given the Prisma models included, running the following:

      const results = await db.userToObjective.findMany({
        include: {
          _count: {
            select: {
              votes: true,
            },
          },
        },
      })

Gives the following error:

Raw query failed. Code: `42804`. Message: `db error: ERROR: argument of JOIN/ON must be type boolean, not type record`

Expected behavior

It should successfully return an array of objects that include the vote count.

Prisma information

model User {
        id             	Int      @id @default(autoincrement())
	votes		Vote[]
}

model Objective {
	id                 Int       			@default(autoincrement()) @id
	name               String		 	@unique
}

model UserToObjective {
	user               User      			@relation(fields: [userId], references: [id])
	userId             Int
	objective          Objective 			@relation(name: "UserObjectives", fields: [objectiveId], references: [id])
	objectiveId        Int
	votes		   Vote[]			
	
	@@id([userId,objectiveId])
}

model Vote {
	createdAt          	DateTime  			@default(now())
	user               	User      			@relation(fields: [userId], references: [id])
	userId             	Int
	userObjective		UserToObjective			@relation(fields: [objectiveId, followerId], references: [objectiveId, userId])
	objectiveId		Int
	followerId              Int

	@@id([userId, objectiveId])
}

Environment & setup

• OS: Windows 10, 64-bit
• Database: PostgreSQL 12
• Node.js version: 14.15.0

Prisma Version

2.23.0

[pantharshit00]

Sorry for the late reply here, I can reproduce the crash. We need to fix the query generation.
Thanks for the detailed reproduction.

I tried it on 2.27.0-dev.33