NOT condition leaks out of its desired bounds

[arunlodhi]

Bug description

Consider the following mongodb query:

prisma.videos.findMany({
    where: {
      OR: [
        {
          labels: {
            hasSome: ['tag1'],
          },
          NOT: {
            labels: {
              hasSome: ['tag2'],
            },
          },
        },
        {
          title: 'abcd',
        },
      ],
    },
  })

The aim is to find all videos where either:

• labels include tag1 and does not include tag2
• or title is 'abcd'

However, the query that is passed to mongo db does the following: select all documents where either:

• labels include tag1 and does not include tag2
• or title is NOT 'abcd'

The raw mongo query run by prisma is as follows:

db.app_videos.aggregate(
  [
    {
      "$match": {
        "$expr": {
          "$or": [
            {
              "$and": [
                {
                  "$and": [
                    {
                      "$or": [
                        {
                          "$in": [
                            "bridal",
                            {
                              "$ifNull": [
                                "$labels",
                                []
                              ]
                            }
                          ]
                        }
                      ]
                    },
                    {
                      "$ne": [
                        "$labels",
                        "$$REMOVE"
                      ]
                    }
                  ]
                },
                {
                  "$and": [
                    {
                      "$and": [
                        {
                          "$not": {
                            "$or": [
                              {
                                "$in": [
                                  "beauty",
                                  {
                                    "$ifNull": [
                                      "$labels",
                                      []
                                    ]
                                  }
                                ]
                              }
                            ]
                          }
                        },
                        {
                          "$ne": [
                            "$labels",
                            "$$REMOVE"
                          ]
                        }
                      ]
                    }
                  ]
                }
              ]
            },
            {
              "$and": [
                {
                  "$ne": [  // <--------------------Faulty Condition
                    "$title",
                    {
                      "$literal": "abcd"
                    }
                  ]
                },
                {
                  "$ne": [
                    "$title",
                    "$$REMOVE"
                  ]
                }
              ]
            }
          ]
        }
      }
    },
    {
      "$project": {
        "_id": 1,
        "createdAt": 1,
        "updatedAt": 1,
        "deletedAt": 1,
        "status": 1,
        "createdBy": 1,
        "publicVideoId": 1,
        "teamId": 1,
        "title": 1,
        "labels": 1,
        "videoAssetId": 1,
        "videoUrl": 1,
        "thumbnailAssetId": 1,
        "thumbnailUrl": 1,
        "streamType": 1,
        "startDate": 1,
        "endDate": 1,
        "visibility": 1
      }
    }
  ]
);

I have noticed that the NOT condition leaks out of its bounds. If I just switch the order of objects inside the OR array, the query works perfectly fine:

prisma.videos.findMany({
    where: {
      OR: [
        {
          title: 'abcd',
        },
        {
          labels: {
            hasSome: ['tag1'],
          },
          NOT: {
            labels: {
              hasSome: ['tag2'],
            },
          },
        },
      ],
    },
  })

db.app_videos.aggregate(
  [
    {
      "$match": {
        "$expr": {
          "$or": [
            {
              "$and": [
                {
                  "$eq": [ // <-------------------- Correct condition
                    "$title",
                    {
                      "$literal": "abcd"
                    }
                  ]
                },
                {
                  "$ne": [
                    "$title",
                    "$$REMOVE"
                  ]
                }
              ]
            },
            {
              "$and": [
                {
                  "$and": [
                    {
                      "$or": [
                        {
                          "$in": [
                            "bridal",
                            {
                              "$ifNull": [
                                "$labels",
                                []
                              ]
                            }
                          ]
                        }
                      ]
                    },
                    {
                      "$ne": [
                        "$labels",
                        "$$REMOVE"
                      ]
                    }
                  ]
                },
                {
                  "$and": [
                    {
                      "$and": [
                        {
                          "$not": {
                            "$or": [
                              {
                                "$in": [
                                  "beauty",
                                  {
                                    "$ifNull": [
                                      "$labels",
                                      []
                                    ]
                                  }
                                ]
                              }
                            ]
                          }
                        },
                        {
                          "$ne": [
                            "$labels",
                            "$$REMOVE"
                          ]
                        }
                      ]
                    }
                  ]
                }
              ]
            }
          ]
        }
      }
    },
    {
      "$project": {
        "_id": 1,
        "createdAt": 1,
        "updatedAt": 1,
        "deletedAt": 1,
        "status": 1,
        "createdBy": 1,
        "publicVideoId": 1,
        "teamId": 1,
        "title": 1,
        "labels": 1,
        "videoAssetId": 1,
        "videoUrl": 1,
        "thumbnailAssetId": 1,
        "thumbnailUrl": 1,
        "streamType": 1,
        "startDate": 1,
        "endDate": 1,
        "visibility": 1
      }
    }
  ]
)

How to reproduce

Create a database with the provided schema and load the following data:

await prisma.videos.createMany({
  data: [
    {title: 'test1', labels: ['tag1', 'random']},
    {title: 'test2', labels: ['tag2', 'random']},
    {title: 'test3', labels: ['tag1', 'tag2']},
    {title: 'test4', labels: ['tag3']},
  ]
})

const results = await prisma.videos.findMany({
    where: {
      OR: [
        {
          labels: {
            hasSome: ['tag1'],
          },
          NOT: {
            labels: {
              hasSome: ['tag2'],
            },
          },
        },
        {
          title: 'abcd',
        },
      ],
    },
  });

console.log(results);

Ideally, we should get only the first row, but prisma returns all the rows.

Expected behavior

The query should not put not operator for the title condition.

Prisma information

model Videos {
  id        String    @id @default(auto()) @map("_id") @db.ObjectId
  createdAt DateTime  @default(now()) @db.Timestamp
  updatedAt DateTime  @updatedAt @db.Timestamp

  title            String
  labels           String[]

  @@map("app_videos")
}

const results = await prisma.videos.findMany({
    where: {
      OR: [
        {
          labels: {
            hasSome: ['tag1'],
          },
          NOT: {
            labels: {
              hasSome: ['tag2'],
            },
          },
        },
        {
          title: 'abcd',
        },
      ],
    },
  })

Environment & setup

• OS: MacOS 10.15.7
• Database: MongoDB (Hosted on Atlas CLoud)
• Node.js version: 18.16.1

Prisma Version

5.6.0

[arunlodhi]

Any updates here?

Seems like a pretty serious bug to me. I have to be very careful now when using NOT conditions. If I can't rely on the ORM to generate the raw query correctly when using simple conditions, there is no point of using it.

If someone could point me to the right sections in the codebase, I would be happy to fix this and open a PR.

[SevInf]

Can confirm the bug on latest main (5.11.0-dev).
Also can confirm that it's mongo-specific problem, indentical query on postgres returns correct result.

Internal link