Prisma-client-js: PANIC: 'NaN' f64 is not a Decimal: Error { message: "Invalid decimal: invalid character" }

[DekzVt]

Hi Prisma Team! My Prisma Client just crashed. This is the report:

* I've redacted business logic sensitive queries *

I was able to fix the issue by changing a float8 in my PSQL db that was being queried from NaN to 0.

Versions

| Name | Version |
|----------|--------------------|
| Node | v14.5.0 |
| OS | debian-openssl-1.1.x|
| Prisma | 2.1.3 |

Logs

2020-07-21T17:52:27.243Z prisma-client {
  engineConfig: {
    cwd: '/app/prisma',
    enableDebugLogs: false,
    enableEngineDebugMode: undefined,
    datamodelPath: '/app/node_modules/.prisma/client/schema.prisma',
    prismaPath: undefined,
    engineEndpoint: undefined,
    generator: {
      name: 'client',
      provider: 'prisma-client-js',
      output: '/app/node_modules/@prisma/client',
      binaryTargets: [],
      previewFeatures: [],
      config: {}
    },
    showColors: false,
    logLevel: 'info',
    logQueries: true,
    flags: [],
    clientVersion: '2.1.3',
    enableExperimental: []
  }
}
2020-07-21T17:54:05.040Z prisma-client Prisma Client call:
2020-07-21T17:54:05.041Z prisma-client prisma.**redacted**.findMany({
  cursor: undefined,
  take: 5
})
2020-07-21T17:54:05.041Z prisma-client Generated request:
2020-07-21T17:54:05.041Z prisma-client query {
  **redacted**
}

2020-07-21T17:54:05.046Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-debian-openssl-1.1.x are fine
2020-07-21T17:54:05.114Z prisma-client Client Version 2.1.3
2020-07-21T17:54:05.115Z prisma-client Engine Version e11114fa1ea826f9e7b4fa1ced34e78892fe8e0e
2020-07-21T17:55:08.329Z prisma-client Prisma Client call:
2020-07-21T17:55:08.330Z prisma-client prisma.**redacted**.findMany({
 **redacted**
})
2020-07-21T17:55:08.330Z prisma-client Generated request:
2020-07-21T17:55:08.330Z prisma-client query {
  ** redacted**
}

2020-07-21T17:55:13.226Z prisma-client Prisma Client call:
2020-07-21T17:55:13.226Z prisma-client prisma.user_tax_detail.findMany({
  where: {
    user_id: {
      equals: 2
    }
  }
})
2020-07-21T17:55:13.226Z prisma-client Generated request:
2020-07-21T17:55:13.226Z prisma-client query {
 **redacted **
}

2020-07-21T17:55:27.559Z prisma-client Prisma Client call:
2020-07-21T17:55:27.559Z prisma-client prisma.**redacted**.findMany({
  **redacted**
})
2020-07-21T17:55:27.559Z prisma-client Generated request:
2020-07-21T17:55:27.559Z prisma-client query {
  **redacted**
}

2020-07-21T17:55:43.570Z prisma-client Prisma Client call:
2020-07-21T17:55:43.571Z prisma-client prisma.**redacted**.findMany({
  **redacted**
})
2020-07-21T17:55:43.571Z prisma-client Generated request:
2020-07-21T17:55:43.571Z prisma-client query {
  **redacted**
}

2020-07-21T17:55:50.919Z prisma-client Prisma Client call:
2020-07-21T17:55:50.919Z prisma-client prisma.**redacted**.findMany({
  **redacted**
})
2020-07-21T17:55:50.919Z prisma-client Generated request:
2020-07-21T17:55:50.919Z prisma-client query {
  **redacted**
}

[hayes]

I am also running into this bug. Because of the way this crashes, it can cause multiple queries to fail that have been batched together, even if only one of those queries selects a NaN value.

[janpio]

Can you maybe add a schema and data SQL here that can be used to replicate the problem?

[hayes]

@janpio I created this https://github.com/hayes/prisma-nan-bug-example

To reproduce, create a new postgres db, and run the prisma migrate up then in index.ts uncomment the alter table statement. and run the script.

This will create a table with a double precision column. The numeric type that prisma uses for floats seems to work okay (NaN is cast to 0).

Once you have the table set up, you can uncomment the other line to add a row with a NaN. When this row is queried, you should see the error describe above.

[divyenduz]

@hayes Thanks for the detailed reproduction. I am able to reproduce this

[pimeys]

This is an interesting issue and might lead to a larger refactoring in Quaint, and from there on in Query Engine Core.

Our queries in Quaint treat all floating point values as decimals. Decimal type can represent floats in an accurate manner, saving us from rounding errors what you usually witness with floats.

Even more importantly, the core functions in the query engine rely on things to be something implementing the Hash trait. This means, the engine uses hashmaps or hashsets with values, and the collections require the keys to implement Hash before you can use them as keys!

So why can't we use floats here? A Hash is something that returns a unique value for every single different hashable object. So if 1.23 returns "asedsada", no other float should return the same hash string. The problem then is, how a float includes a special value NaN, which represents a number that cannot be represented as a float. One good example is a calculation of 1.23 / 0.0, which is a division by zero, resulting a NaN value. Now, we could imagine a hash value for NaN, but this means we can only have one value for NaN and this is where we're in trouble! Thinking of different ways of getting into NaN is at least _every_ division by zero operation, square root of _any_ negative number and so on.

This means we have a huge amount of collisions, breaking the guarantees of the Hash!

Now, if we want to support NaN values in the database, we should considerably refactor our Value to hold Float, Double and Decimal as separate types, but also get rid of all Hash requirements in the core for Value. It's mainly a question for @dpetrick how heavy refactoring it would be for the core.

[pimeys]

Let me rephrase a bit after discussing about the Hash requirements for Value with @dpetrick. Any Value can be a primary key and we map children for parent objects with the primary key Values. If there'd be a floating point primary key, the database would allow us using NaN there, meaning we'd have a non-unique primary key comparable to NULL, breaking the whole relation model of Prisma.

Better to panic than break the model here...

[pimeys]

For future us, when we're thinking of solving this, here's what PostgreSQL manual says about NaN:

In addition to ordinary numeric values, the numeric type allows the special value NaN, meaning "not-a-number". Any operation on NaN yields another NaN. When writing this value as a constant in an SQL command, you must put quotes around it, for example UPDATE table SET x = 'NaN'. On input, the string NaN is recognized in a case-insensitive manner.

and

Note: In most implementations of the "not-a-number" concept, NaN is not considered equal to any other numeric value (including NaN). In order to allow numeric values to be sorted and used in tree-based indexes, PostgreSQL treats NaN values as equal, and greater than all non-NaN values.

MySQL doesn't support NaN values for floats, SQLite kind of does with a special flag and for Microsoft SQL Server I found this:

Looking on Microsoft Connections, I found an interesting item. It appears that MS has chosen to no longer support NaN and Infinity in the database in SQL Server 2005. In fact, a request had been made to restore the 2000 functionality. There is, at least, one organization which is using SQL Server to store engineering data and who has the need to occassionally store Infinities in the database. Evidently they are not performing any ad hoc queries or calculation within the database.

So, it's very specific for PostgreSQL and only with their numeric type.

[janpio]

To conclude this discussion here:
Prisma currently does not support NaN values. I created https://github.com/prisma/prisma/issues/3492 to track support for these values. Please subscribe to that issue if you want to be updated about progress on implementation of that feature. Thanks for creating this issue though, this surfaced this missing functionality to us @DekzVt and @hayes.

[pantharshit00]

Closing in favour of prisma/prisma#3492