Self update query is giving wrong update : 500.03 + 0.03 becomes 500.05999999999995

Other MongoDB Topics
,
1 
rs5 [direct: primary] wl_gp_local> db.player_balance.updateMany({player_id: "646210805c9d4fc15d0fcd87" }, { $set: { balance: 500.03 } })
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 0,
  upsertedCount: 0
}
rs5 [direct: primary] wl_gp_local> db.player_balance.find({player_id:"646210805c9d4fc15d0fcd87"});
[
  {
    _id: ObjectId('646210805c9d4fc15d0fcd8a'),
    tenant_id: '6461fc3c4ff7f71988060223',
    player_id: '646210805c9d4fc15d0fcd87',
    balance: 500.03,
    provider_id: null,
    updated_at: ISODate('2023-05-15T10:59:12.105Z'),
    created_at: ISODate('2023-05-15T10:59:12.105Z')
  }
]
rs5 [direct: primary] wl_gp_local> db.player_balance.updateMany({player_id: "646210805c9d4fc15d0fcd87" }, { $inc: { balance: 0.03 } })
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}
rs5 [direct: primary] wl_gp_local> db.player_balance.find({player_id:"646210805c9d4fc15d0fcd87"});
[
  {
    _id: ObjectId('646210805c9d4fc15d0fcd8a'),
    tenant_id: '6461fc3c4ff7f71988060223',
    player_id: '646210805c9d4fc15d0fcd87',
    balance: 500.05999999999995,
    provider_id: null,
    updated_at: ISODate('2023-05-15T10:59:12.105Z'),
    created_at: ISODate('2023-05-15T10:59:12.105Z')
  }
]
rs5 [direct: primary] wl_gp_local> db.player_balance.updateMany({player_id: "646210805c9d4fc15d0fcd87" }, { $set: { balance: 500.04 } })
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}
rs5 [direct: primary] wl_gp_local> db.player_balance.find({player_id:"646210805c9d4fc15d0fcd87"});
[
  {
    _id: ObjectId('646210805c9d4fc15d0fcd8a'),
    tenant_id: '6461fc3c4ff7f71988060223',
    player_id: '646210805c9d4fc15d0fcd87',
    balance: 500.04,
    provider_id: null,
    updated_at: ISODate('2023-05-15T10:59:12.105Z'),
    created_at: ISODate('2023-05-15T10:59:12.105Z')
  }
]
rs5 [direct: primary] wl_gp_local> db.player_balance.updateMany({player_id: "646210805c9d4fc15d0fcd87" }, { $inc: { balance: 0.03 } })
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}
rs5 [direct: primary] wl_gp_local> db.player_balance.find({player_id:"646210805c9d4fc15d0fcd87"});
[
  {
    _id: ObjectId('646210805c9d4fc15d0fcd8a'),
    tenant_id: '6461fc3c4ff7f71988060223',
    player_id: '646210805c9d4fc15d0fcd87',
    balance: 500.07,
    provider_id: null,
    updated_at: ISODate('2023-05-15T10:59:12.105Z'),
    created_at: ISODate('2023-05-15T10:59:12.105Z')
  }
]
rs5 [direct: primary] wl_gp_local>

when I try to self update(increment) balance column when value is between 500.01 to 500.03 by value 0.03 then balance becomes 500.05999999999995. and when I try to self update of value greater than or equal to 500.04 by 0.03 then result is 500.07. why such rounding up is happening before getting added and sometimes rounding not happening after added.

Please check all above queries you will understand the scenario I am trying to explain.

2

Hello @vishal_mote, Welcome to the MongoDB community forum,

That error is due to the limitations of floating-point arithmetic in computers.

Floating-point numbers are represented in a binary format with a limited number of bits to store the significand (the digits of the number) and the exponent (the power of 10).

In this case, the sum 500.03 + 0.03 cannot be represented exactly in binary form with the limited precision of floating-point numbers. The result is stored as an approximation that is slightly less than 500.06.

This is not specific to MongoDB. It occurs in all programming languages that use floating-point arithmetic, ex. see in JavaScript:

image

To avoid this issue, you can store value in the Decimal data type,

Ex:

{ $set: { balance: NumberDecimal("500.03") } }

And

{ $inc: { balance: NumberDecimal("0.03") } }

Refer to the documentation of Decimal:

Refer to the Article:

1 Like