Prisma-client-js: Mutating a `Float` with a small value results on a very different value written to Postgres

[snake575]

Hi 👋 I encountered an error when creating or updating a small Float on Postgres.

Repo: snake575/prisma-float-error

• Prisma: 2.0.0-preview022
• Runtime: query-engine-debian-openssl-1.1.x
• PostgreSQL 12.2

Prisma schema:

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

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

model Transaction {
  id       Int    @id @default(autoincrement())
  amount   Float
  currency String
}

Mutation:

const transaction = await prisma.transaction.create({
  data: {
    amount: 0.00006927,
    currency: 'BTC',
  },
})
console.log({ transaction })

Wrong amount:

{ transaction: { id: 1, amount: 6.927e-17, currency: 'BTC' } }

Postgres log:

2020-03-01 23:02:16.045 -03 [278] LOG: execute s0: INSERT INTO "public"."Transaction" ("amount","currency") VALUES ($1,$2) RETURNING "public"."Transaction"."id"
2020-03-01 23:02:16.045 -03 [278] DETAIL: parameters: $1 = '0.00000000000000006927', $2 = 'BTC'

[pantharshit00]

Thanks for the detailed write-up and the reproduction repository. I can confirm this issue :)

[tomhoule]

I managed to reproduce and fix the issue, it's on this branch: https://github.com/prisma/prisma-engines/pull/538 - it contains a lot more work on mapping postgres native types to prisma types, and will be merged soon.

[snake575]

Thanks, that was a quick fix! 😃

I found another problem that may be related

const transaction = await prisma.transaction.create({
  data: {
    amount: 0.00071832,
    currency: 'BTC',
  },
})
console.log({ transaction })

Returns correct amount

{ transaction: { id: 2, amount: 0.00071832, currency: 'BTC' } }

But a sightly different value is written to Postgres

2020-03-06 17:00:27.304 -03 [33] LOG: execute s0: INSERT INTO "public"."Transaction" ("amount","currency") VALUES ($1,$2) RETURNING "public"."Transaction"."id"
2020-03-06 17:00:27.304 -03 [33] DETAIL: parameters: $1 = '0.0007183200000000001', $2 = 'BTC

[janpio]

@snake575 Best create a new issue an link to it here instead - this will make it much easier for us (and @tomhoule) to track the problem and the fix. Thanks for reporting!

[tomhoule]

The second issue should be fixed as well now (latest alpha), but indeed, let's create another issue if it's still happening.

[janpio]

(@tomhoule Already happened: https://github.com/prisma/prisma-client-js/issues/555 Might want to close this one as well then later if really fixed)

[tomhoule]

I can confirm this is fixed with the engine changes I mentioned above. Confirmed with manual testing on alpha.927.

[tomhoule]

The fixes should be in the next preview release :)

[snake575]

@janpio I confirm this is fixed on alpha 927, it's also fixed on preview-24

[janpio]

Thanks @snake575 <3

[abisuq]

@janpio It was right when writing to the database, but it was wrong when reading. (version 2.0.1)

[snake575]

@janpio It was right when writing to the database, but it was wrong when reading. (version 2.0.1)

I can confirm this, the original problem was solved but since prisma 2.1.0 the value is created correctly on the database but returns a different value to the client (2.0.1 on both @prisma/client and @prisma/cli works for me though).

I updated the repro: snake575/prisma-float-error with prisma 2.1.3