Fields are ignored in WHERE clause

[jvvcn]

Bug description

We use the following model for a table with uniq compound key by employeeId & paymentDate

model Payslip {
  id  String @id @default(uuid())
  status String
  employeeId  String
  employee  Employee  @relation(fields: [employeeId], references: [id])
  paymentDate  DateTime
  # rest of fields
  @@unique([employeeId, paymentDate], name: "uniqKeyByEmployeeIdAndPaymentDate")
}

Queries like findFirst, findMany, updateMany (maybe some other, haven't check yet) ignore some fields in a where clause.

Query examples:

const { count } = await this.prisma.payslip.updateMany({
    where: {
      AND: [
        { employeeId: { in: pluck('id', employees) } },
        { status: Status.Uploaded },
        { paymentDate: new Date(paymentDate) },
        { publishedAt: null },
      ],
    },
    data: {
      status: Status.Published,
      publishedAt: new Date(),
      updatedAt: new Date(),
  },
})

const { count } = await this.prisma.payslip.updateMany({
  where: {
    status: Status.Uploaded,
    paymentDate: new Date(paymentDate),
    employeeId: { in: pluck('id', employees) },
    publishedAt: null,
  },
  data: {
    status: Status.Published,
    publishedAt: new Date(),
    updatedAt: new Date(),
  },
})

The output from logging is the following

SELECT `payslip`.`Payslip`.`id` FROM `payslip`.`Payslip` WHERE (`payslip`.`Payslip`.`status` = ? AND 1=1 AND `payslip`.`Payslip`.`employeeId` IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) AND `payslip`.`Payslip`.`publishedAt` IS NULL) ["UPLOADED","1","10","103","104","11","12","13","14","15","16","17","18","19","19007763-e05e-472e-a840-c3e40dcedc91","1bc472b4-2fed-456b-9a0a-63de068edb66","2","20","21","22","23","24","25","26","3","302bb32d-03ff-41e8-9915-185b65d77b67","31","32","36","39","4","40","41","44","47","48","49","5","50","51","54","55","6","69","7","70","71","72","73","74","8","88","89","9","e01e5c65-2315-47eb-ae71-db446c180279","e5edb7f1-5de0-45d0-b5f1-265b6dd9ebb3","eebaac6e-afd8-489a-9804-127e697ca00c"]


UPDATE `payslip`.`Payslip` SET `updatedAt` = ?, `status` = ?, `publishedAt` = ? WHERE `payslip`.`Payslip`.`id` IN (?,?,?,?,?,?,?,?,?,?,?) [2020-10-21 07:25:04.376 UTC,"PUBLISHED",2020-10-21 07:25:04.376 UTC,"03882468-0abd-434c-afb1-f74b0549e498","18b0b22d-eaf2-4621-89d5-4f99dae8be21","5d877156-8040-411e-af3e-fd5f66a5b367","7b65281a-0ed1-45a8-bb66-a461ded2461e","919a15b9-a5d2-497e-8316-8d77bd7febfd","9ddaa446-733c-4437-a2ab-23dbbe60d14d","a196dc31-73a1-4988-9d79-b47e63de89dd","bbb39f70-4d1b-440c-a714-69ed415e5780","bf395bf3-c019-4ce4-97f9-2b716155b562","e715fa49-0758-47f2-a053-e8039078c818","ef96351e-0fc2-45f3-b2f6-9edffe2b98b6"]

From the log fields status, paymentDate, publishedAt are missed from WHERE clause

Expected behavior

Fields which are in a where clause should not be ignored.

Environment & setup

• @prisma/cli - 2.9.0
• @prisma/client - 2.9.0
• OS: Alpine Linux v3.11
• Database: MySQL
• Node.js version: v12.18.4

[P4sca1]

Are you sure the query you posted is the only query generated by your code? It could be that Prisma selects the ids with the correct where clause before and then does another query which only filters by those ids.

[jvvcn]

@P4sca1 Select (from another table Employee) goes before, then I just pass ids into update query and the update query just drop all other fields except ids. I'm pretty sure that fields are dropped from where clause and the log is for exact query from above and I don't know why

[jvvcn]

@P4sca1 You were right there is a select, but the issue still remains, paymentDate is dropped from select query and instead of it I see 1=1. I cant understand why, any ideas?

[P4sca1]

If only paymentDate is dropped, then this issue is likely related: #3987

[pantharshit00]

I can confirm this, looks like value of new Date() is being wrongly parsed internally.

Reproduction: https://github.com/harshit-test-org/prisma-issue-3985

[pantharshit00]

@matthewmueller This is unrelated to the date format. This related to client generating the wrong query which is sent to the engine.

When using new Date() (aka passing a plain js object in the reproduction), the following query is sent to the engine which has empty date field:

mutation {
  prisma-client   updateManyPayslip(
  prisma-client     where: {
  prisma-client       AND: [
  prisma-client         {
  prisma-client           employeeId: {
  prisma-client             in: [""]
  prisma-client           }
  prisma-client         },
  prisma-client         {
  prisma-client           status: "uploaded"
  prisma-client         },
  prisma-client         {
  prisma-client           paymentDate: {
  prisma-client
  prisma-client           }
  prisma-client         },
  prisma-client         {
  prisma-client           publishedAt: null
  prisma-client         }
  prisma-client       ]
  prisma-client     }
  prisma-client     data: {
  prisma-client       status: "published"
  prisma-client       publishedAt: "2020-11-09T13:09:25.951Z"
  prisma-client       updatedAt: "2020-11-09T13:09:25.951Z"
  prisma-client     }
  prisma-client   ) {
  prisma-client     count
  prisma-client   }
  prisma-client }

But when you pass ISO date string to the client new Date().toISOString(), it sends this query to the engine which returns the desired result:

mutation {
  prisma-client   updateManyPayslip(
  prisma-client     where: {
  prisma-client       AND: [
  prisma-client         {
  prisma-client           employeeId: {
  prisma-client             in: [""]
  prisma-client           }
  prisma-client         },
  prisma-client         {
  prisma-client           status: "uploaded"
  prisma-client         },
  prisma-client         {
  prisma-client           paymentDate: "2020-11-09T13:08:04.703Z"
  prisma-client         },
  prisma-client         {
  prisma-client           publishedAt: null
  prisma-client         }
  prisma-client       ]
  prisma-client     }
  prisma-client     data: {
  prisma-client       status: "published"
  prisma-client       publishedAt: "2020-11-09T13:08:04.703Z"
  prisma-client       updatedAt: "2020-11-09T13:08:04.703Z"
  prisma-client     }
  prisma-client   ) {
  prisma-client     count
  prisma-client   }
  prisma-client }

Diff:

So there is a bug in client query generation. cc/ @timsuchanek

[dpetrick]

For completeness: I can't reproduce the behavior on the engine side with correct inputs.