Here's a demonstration of what can happen in programming languages and why you need to consider it...
Python example:
$ python -c "print('%0.18f' % (0.1 * 0.2))"
0.020000000000000004
^^ PRECISION-LOSS
Node (JavaScript) example:
$ node -e 'console.log(0.1 * 0.2)'
0.020000000000000004
^^ PRECISION-LOSS
$ mongo --nodb --quiet -eval '0.1 * 0.2'
0.020000000000000004
^^ PRECISION-LOSS!
Java example (using Java's newish JShell tool):
$ printf "System.out.println(0.1 * 0.2); \n/ex\n" | jshell -q
0.020000000000000004
^^ PRECISION-LOSS
$ printf "System.out.println(0.1 * 0.2); \n/ex\n" | jshell -q
0.020000000000000004
^^ PRECISION-LOSS
None of this is really a surprise given that most of these high-level programming languages are built using C (or C++), which invariably provides the fundamental building block types for floats and doubles.
$ printf "int main(void){ printf(\"%%.18lf\\\n\", (0.1 * 0.2)); return 0; }" | cc -w -x c -o multiply - && ./multiply
0.020000000000000004
^^ PRECISION-LOSS
For example, Java has the BigDecimal library class for this reason:
$ printf "System.out.println((new BigDecimal(\"0.2\")).multiply(new BigDecimal(\"0.1\"))); \n/ex\n" | jshell -q
0.02
^^ NO LOSS
When it comes to databases, the same challenges exist when using fields with floating point values. There may be the need to store and retrieve such fields without loss of precision, whilst enabling arithmetic to be conducted on these fields.
Example of using a standard JavaScript/JSON float type for a field in a MongoDB database:
$ mongo
> db.records.drop()
> db.records.insert({val: 0.2})
> db.records.findOne()
{
"_id" : ObjectId("5b0a7c24d3dac6c87c0d4a4b"),
"val" : 0.2
}
> id = db.records.findOne()._id
> db.records.update({_id: id}, {$mul: {val: 0.1}})
> db.records.findOne()
{
"_id" : ObjectId("5b0a7c24d3dac6c87c0d4a4b"),
"val" : 0.020000000000000004
}
> db.records.findOne().val
0.020000000000000004
^^ PRECISION-LOSS
Example of using a BSON decimal128 type for a field in a MongoDB database:
$ mongo
> db.records.drop()
> db.records.insert({val: NumberDecimal("0.2")})
> db.records.findOne()
{
"_id" : ObjectId("5b0a7ce9d3dac6c87c0d4a4c"),
"val" : NumberDecimal("0.2")
}
> id = db.records.findOne()._id
> db.records.update({_id: id}, {$mul: {val: NumberDecimal("0.1")}})
> db.records.findOne()
{
"_id" : ObjectId("5b0a7ce9d3dac6c87c0d4a4c"),
"val" : NumberDecimal("0.02")
}
> db.records.findOne().val
NumberDecimal("0.02")
^^ NO LOSS
It could be that the loss of precision does not have a major impact on a particular application and the business purpose it is used for. However, in some cases, especially for financial or scientific applications, the ability to store and process decimal fields without loss of precision is likely to be critical.
Song for today: Quit It by Strand of Oaks