Strange different results in aggregate summing through $sum vs $reduce

Collection has only one document :

{
     "_id" : 1.0,
     "data" : [10.42, 40.07, 98.565, 69.8]
}

Make and run such aggregate:

db.test_data.aggregate(
     [
         { "$addFields" : {
                 "sum1" : { "$sum" : "$data" },
                 "sum2" : {
                     "$reduce" : {
                         "input" : "$data",
                         "initialValue" : 0.0,
                         "in" : { "$add" : ["$$value","$$this"]}
                     }
                 }
             }
         }
     ]
);

Results:

{
     "_id" : 1.0,
     "data" : [
         10.42,
         40.07,
         98.565,
         69.8
     ],
     "sum1" : 218.855,
     "sum2" : 218.85500000000002
}

It is clear that the internal representation of floating numbers can result in sum2,
but why are the results different at all?

Comments?

Interesting find @Vadim_Shumilin!

I believe what you have seen in your results is a side effect of binary rounding errors. I believe this is a universal behaviour, so it’s not limited to MongoDB. In this particular case I believe it’s a direct result of two things:

  1. https://floating-point-gui.de/
  2. The order that the summing is done

There are more details regarding the ordering of the sum in the stack overflow post for example. For reference as well, I did the $sum and $add as seperate fields for the same data array which both result in the same value:

testdb> db.sumcoll.aggregate({
  '$addFields': {
    sumField: { '$sum': [ 10.42, 40.07, 98.565, 69.8 ] },
    addField: { '$add': [ 10.42, 40.07, 98.565, 69.8 ] }
  }
})
[
  {
    _id: ObjectId("63c76ab383424604d536bf3b"),
    data: [ 10.42, 40.07, 98.565, 69.8 ],
    sumField: 218.855,
    addField: 218.855
  }
]

Hope the above helps or sheds some light on what has happened.

Regards,
Jason

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.