Bug description

Raw query using PostGIS functions fails with the following error when using float values as bind parameters.

Invalid `prisma.$queryRaw()` invocation:

Raw query failed. Code: `22P03`. Message: `db error: ERROR: incorrect binary data format in bind parameter 1`

How to reproduce

I have a raw query using a PostGIS function:

export const calculate = async (
  longitude: number,
  latitude: number,
  radiusInMeters: number,
) => {
  return prisma.$queryRaw<{ sum: number }[]>`
    SELECT SUM("range")::text
    FROM "Locations" AS s 
    WHERE ST_DWithin(s.coordinates, ST_MakePoint(${longitude}, ${latitude}), ${radiusInMeters})
  `;
};

This works perfectly fine when called via a script, e.g.

calculate(10.1, 50.1, 5000).then(console.log);

Now, I'm using this function in a Next.js application (with tRPC) and the user provides the longitude and latitude parameters via a form. Whenever I send non-integer (float) numbers into the function, the query fails with:

Raw query failed. Code: `22P03`. Message: `db error: ERROR: incorrect binary data format in bind parameter 1`

Integer values work all the time.

I double-checked that the passed in values are indeed floats and even tried explicitly parsing them.
I tried passing hard-coded float values from my API route to the function instead of the ones provided by the user via the form.
I added type-casting to the SQL query to have the values interpreted as floats.

But no matter what I try, the error keeps popping up.

I went ahead and turned on query logging in my Postgres and for all non-working queries, the bind parameters are completely absent:

2022-12-04 09:47:55.791 UTC [73] LOG:  statement: SELECT 1
2022-12-04 09:47:55.796 UTC [73] ERROR:  incorrect binary data format in bind parameter 1
2022-12-04 09:47:55.796 UTC [73] CONTEXT:  unnamed portal parameter $1
2022-12-04 09:47:55.796 UTC [73] STATEMENT:
	    SELECT SUM("range")::text AS sum
	    FROM "Locations" AS s
	    WHERE ST_DWithin(s.coordinates, ST_MakePoint($1, $2), $3)

And here's where things get really strange: After restarting my Next.js application the error suddenly went away. After more debugging, I found out that it depends on the order of putting in values. If I start my Next.js application and run the query with integer values (which always succeeds) and afterwards pop in a float value, the query breaks with the aforementioned error. If I then restart my Next.js application again and put in float values right from the start, the
query works. So, my assumption is that this could be some internal optimization (caching of data types?) done by the Prisma engine?

Thanks for any hints!

Expected behavior

The raw query should work every time.

Prisma information

The schema looks something like this:

model Locations {
  id                              String                                 @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  range                           Decimal?                               @db.Decimal(16, 2)
  coordinates                     Unsupported("GEOGRAPHY(Point)")?

  @@index([coordinates], name: "coordinates_idx", type: Gist)
}

My prisma module used inside the Next.js application is based on the create-t3-app one:

import { PrismaClient } from '@prisma/client';

declare global {
  // allow global `var` declarations
  // eslint-disable-next-line no-var
  var prisma: PrismaClient | undefined;
}

export const prisma =
  global.prisma ||
  new PrismaClient({
    log:
      process.env.NODE_ENV === 'development'
        ? ['error', 'warn'] // Add 'query' to log queries
        : ['error'],
  });

export * from '@prisma/client';

if (process.env.NODE_ENV !== 'production') {
  global.prisma = prisma;
}

Environment & setup

• OS: macOS
• Database: PostgreSQL with PostGIS (Docker image: postgis/postgis:14-3.3)
• Node.js version: v18.12.1
• Next.js version: 13.0.0
• tRPC version: 10.4.2
• TurboRepo: 1.6.3

Prisma Version

prisma                  : 4.7.0
@prisma/client          : 4.7.0
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine 39190b250ebc338586e25e6da45e5e783bc8a635 (at ../../node_modules/.pnpm/@prisma+engines@4.7.0/node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine        : migration-engine-cli 39190b250ebc338586e25e6da45e5e783bc8a635 (at ../../node_modules/.pnpm/@prisma+engines@4.7.0/node_modules/@prisma/engines/migration-engine-darwin-arm64)
Introspection Engine    : introspection-core 39190b250ebc338586e25e6da45e5e783bc8a635 (at ../../node_modules/.pnpm/@prisma+engines@4.7.0/node_modules/@prisma/engines/introspection-engine-darwin-arm64)
Format Binary           : prisma-fmt 39190b250ebc338586e25e6da45e5e783bc8a635 (at ../../node_modules/.pnpm/@prisma+engines@4.7.0/node_modules/@prisma/engines/prisma-fmt-darwin-arm64)
Format Wasm             : @prisma/prisma-fmt-wasm 4.7.0-74.39190b250ebc338586e25e6da45e5e783bc8a635
Default Engines Hash    : 39190b250ebc338586e25e6da45e5e783bc8a635
Studio                  : 0.477.0