prisma: Query raw run throws the following error ```incorrect binary data format in bind parameter 3```

Bug description

I’m trying to run a raw query casting the types of some attributes but getting the error describe at the title. I have run the query at the database console and it is working.

How to reproduce

const deliveryPriceCalculated =
      await prisma.$queryRaw<ZipCodeRange>`SELECT * FROM zip_ranges_brazil WHERE
      weight = ${weight}
      AND number_service = ${number_service}
      AND (${zip_origin})::INTEGER BETWEEN zip_origin_start::INTEGER AND zip_origin_end::INTEGER
      AND (${zip_destination})::INTEGER BETWEEN zip_destination_start::INTEGER AND zip_destination_end::INTEGER`

Expected behavior

Run the query and give me the expected data.

Prisma information

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

generator client {
  provider = "prisma-client-js"
}

model ZipCodeRange {
  id                    String   @id @default(uuid())
  number_service        String   @db.VarChar(20)
  region                String   @db.VarChar(100)
  delivery_time         Int
  weight                Float
  value                 Float
  zip_origin_start      String   @db.VarChar(50)
  zip_origin_end        String   @db.VarChar(50)
  zip_destination_start String   @db.VarChar(50)
  zip_destination_end   String   @db.VarChar(50)
  zip_origin_ref        String   @db.VarChar(50)
  zip_destination_ref   String   @db.VarChar(50)
  created_at            DateTime @default(now())
  updated_at            DateTime @default(now())

  @@map("zip_ranges_brazil")
}

Environment & setup

OS: Ubuntu 20
Database: Postgres
Node.js version: 15.4.0

Prisma Version

2.22.1

About this issue

Original URL
State: closed
Created 3 years ago
Reactions: 3
Comments: 15 (6 by maintainers)

Most upvoted comments

Hey,

This issue was fixed by https://github.com/prisma/prisma-engines/pull/2847. It will be available in the next release under the improvedQueryRaw feature flag.

Beware that enabling improvedQueryRaw is a breaking change. Release notes will contain information as to how to upgrade (so will the documentation).

Thanks for reporting 🙏

Weakky on May 5, 2022

Here you got the very exact query i run:

SELECT
         id, name, data, lat, lng, city, "categoryId", "userId",
         (select name from hekete."User" where id="userId") as "userName"
 FROM xxx."Advertisement"
 WHERE
         ST_DistanceSphere(ST_SetSRID(ST_MakePoint(17.03533,51.10773),4326), ST_SetSRID(ST_MakePoint(lng,lat),4326)) / 1000 < 25
         AND
         1=1
 LIMIT 50
 OFFSET 0;

Works OK with pg adapter.

celber on Jul 8, 2021

@pantharshit00 here is the query running with the variables I’m using

Captura de Tela 2021-05-27 às 14 01 53

edilson on May 27, 2021

I do not have logs no more because i have already did workaround for this issue, but this is supereasy to reproduce 😃 Here you have my WHERE clause which works fine in any PG client, yet is lowercased in prisma queryRaw. st_sistancesphere function does not exists so query fails. Funny thing is that prisma logs query as i have written it, but from postgres i get error containing lowercased fragment, so lowercasing must be done at some of the last steps.

const locationQuery = `ST_DistanceSphere(ST_SetSRID(ST_MakePoint(${query.location.lng},${query.location.lat}),4326), ST_SetSRID(ST_MakePoint(lng,lat),4326)) / 1000 < ${query.radius || 25}`;

celber on May 24, 2021

What you wrote above indicates it is not right now because of a bug. I do not think we have documentation or previous issues/discussions about this. Might be worth to actually open a discussion and ask if there is a solution.

janpio on May 21, 2021