$queryRaw/$queryRawUnsafe is modifying query string and causing DB error

[cjscheller]

I am trying to execute the following query as a raw database query due to some limitations of the Prisma client related to groupBy. The database is a Planetscale/Vitess MySQL v8 instance.

SELECT OrderItem.productId, Product.name, Product.description, Product.price, Product.imageUrl, SUM(OrderItem.quantity) as quantity
FROM Invoice
INNER JOIN \`Order\` on Invoice.id = \`Order\`.invoiceId
INNER JOIN OrderItem on \`Order\`.id = OrderItem.orderId
INNER JOIN Product on OrderItem.productId = Product.id
WHERE Invoice.id = ${invoiceId}
GROUP BY OrderItem.productId;

Whenever I execute this query through a $query or $queryRaw, I am receiving the following error (with added formatting):

Invalid `prisma.$queryRawUnsafe()` invocation:

Raw query failed. 
Code: `1055`. 
Message: `target: {db_name}.-.primary: vttablet: rpc error: code = InvalidArgument desc = Expression #2 of SELECT
list is not in GROUP BY clause and contains nonaggregated column 'Product.name' which is not functionally dependent 
on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (errno 1055) (sqlstate 42000) (CallerID: ...: 
Sql: "select OrderItem.productId, Product.`name`, Product.description, Product.price, Product.imageUrl, sum(OrderItem.quantity
 as quantity from Invoice join `Order` on Invoice.id = `Order`.invoiceId join OrderItem on `Order`.id = OrderItem.orderId join
Product on OrderItem.productId = Product.id where 1 != 1 group by OrderItem.productId", 
BindVars: {REDACTED}`

I have attempted:

• Disabling ONLY_FULL_GROUP_BY in MySQL
• Using $queryRaw with templated variables
• Using $queryRawUnsafe with a plain string (no variables)
• Using $queryRawUnsafe with a parameterized query.
• Updating Prisma to 4.15.0

No matter what I do, I can not figure out how/where/why the WHERE clause of the query is updated from WHERE Invoice.id = ${invoiceId} to where 1 != 1. The query works when I run it manually in a MySQL client (with a WHERE clause such as WHERE Invoice.id = 5, and the MySQL 1055 error is only thrown when the where clause is set as 1 != 1.

Can anyone help me figure out what is going on? Thanks!

[ludralph]

Hi @cjscheller 👋

Thanks for raising this question. We would like to reproduce this issue so it would be helpful if you can share the relevant parts of your Prisma schema. I have also shared this issue internally with our engineering team and will get back to you if I receive any information from them.

[cjscheller]

Appreciate the reply. Here's the relevant schema:

datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma"
}

generator client {
  provider        = "prisma-client-js"
  previewFeatures = []
  binaryTargets   = ["native", "rhel-openssl-1.0.x"]
}

model Product {
  id          Int      @id @default(autoincrement())
  name        String   @db.VarChar(255)
  description String?  @db.Text
  price       Float
  imageUrl    String?  @db.VarChar(255)
  active      Boolean  @default(true)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  orderItems       OrderItem[]
}

model Order {
  id         Int      @id @default(autoincrement())
  customerId Int
  invoiceId  Int?
  paid       Boolean  @default(false)
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt

  items    OrderItem[]
  customer Customer    @relation(fields: [customerId], references: [id])
  invoice  Invoice?    @relation(fields: [invoiceId], references: [id])

  @@index([customerId])
  @@index([invoiceId])
}

model OrderItem {
  id        Int      @id @default(autoincrement())
  productId Int
  orderId   Int
  quantity  Int      @default(1)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  order   Order   @relation(fields: [orderId], references: [id])
  product Product @relation(fields: [productId], references: [id])

  @@index(orderId)
  @@index(productId)
}

model Invoice {
  id          Int           @id @default(autoincrement())
  customerId  Int
  periodStart DateTime
  periodEnd   DateTime?
  status      InvoiceStatus @default(OPEN)
  total       Float         @default(0)
  createdAt   DateTime      @default(now())
  updatedAt   DateTime      @updatedAt

  orders   Order[]
  customer Customer @relation(fields: [customerId], references: [id])

  @@index([customerId])
}

[cjscheller]

Would it make sense to convert this to an issue?

[ludralph]

Hi there,

To keep our discussions organized and focused on the most relevant topics, we’re reviewing and tidying up our backlog. As part of this process, we’re closing discussions that haven’t had any recent activity and appear to be outdated.

If this discussion is still important to you or unresolved, we’d love to hear from you! Feel free to reopen it or start a new one with updated details.

For more details about our priorities and vision for the future of Prisma ORM, check out our latest blog post: https://www.prisma.io/blog/prisma-orm-manifesto.

Thank you for your understanding and being part of the community!