[SERVER-26364] Rounding problems with new NumberDecimal Created: 27/Sep/16  Updated: 29/Sep/16  Resolved: 28/Sep/16

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: 3.3.14
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Udo Held Assignee: Geert Bosch
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Windows 10


Operating System: ALL
Steps To Reproduce:

db.fruitsd.drop()
db.fruits.drop()
 
db.fruitsd.insert( {_id: 3, item: "Plums",   price: 6.95, quantity: 7, type : "sweet"  } )
db.fruitsd.insert( {_id: 4, item: "Peaches", price: NumberDecimal("7.49"), quantity: 12, type : "sweet" } )
db.fruitsd.insert( {_id: 5, item: "Bananas", price: NumberDecimal("2.99"), quantity: 5, type : "sweet" } )
 
db.fruits.insert( {_id: 3, item: "Plums",   price: 6.95, quantity: 7, type : "sweet"  } )
db.fruits.insert( {_id: 4, item: "Peaches", price: 7.49, quantity: 12, type : "sweet" } )
db.fruits.insert( {_id: 5, item: "Bananas", price: 2.99, quantity: 5, type : "sweet" } )
 
 
db.fruitsd.aggregate([
  {
    $group: {
	  _id: "$type",
	  totalAmount: { $sum: "$price" },
	  count: { $sum: 1 }
	}
  }
])
 
db.fruits.aggregate([
  {
    $group: {
	  _id: "$type",
	  totalAmount: { $sum: "$price" },
	  count: { $sum: 1 }
	}
  }
])

Sprint: Integration 2016-10-10
Participants:

 Description   

When doing rounding of double numbers together with the new NumberDecimal strange rounding problems show up. I'd expect a mix should return the same results as doubles only or NumberDecimals only.

Mixed output from example:

{ "_id" : "sweet", "totalAmount" : NumberDecimal("17.43000000000000017763568394002505"), "count" : 3 }

Output from doubles only:

{ "_id" : "sweet", "totalAmount" : 17.43, "count" : 3 }

 Comments   
Comment by Geert Bosch [ 28/Sep/16 ]

This Works As Designed. When you add double precision numbers, what you really do is adding binary approximations. In particular, the actual value of 6.95 is 6.95000000000000017763568394002504646778106689453125. If you add that to the other two numbers, you get a final sum of 17.43000000000000017763568394002504646778106689453125. The produced result 17.43000000000000017763568394002505 is actually accurate to the last digit. When you add a larger number of double precision values, you'll typically will not end up with a value that rounds to an integral number of cents. One of the simplest examples is:

> db.money.insert({amount:0.10})
WriteResult({ "nInserted" : 1 })
> db.money.insert({amount:0.10})
WriteResult({ "nInserted" : 1 })
> db.money.insert({amount:0.10})
WriteResult({ "nInserted" : 1 })
> db.money.aggregate([{$group:{_id:"total", amount:{$sum:"$amount"}}}])
{ "_id" : "total", "amount" : 0.30000000000000004 }

Comment by Charlie Swanson [ 28/Sep/16 ]

Hi udoheld,

Thanks for the report. This is an intentional behavior, so I'm going to close this as "Works as Designed".

This is expected because during a $sum, we will do our best to maintain precision, but also to stick with the types already present. Thus, if all of your numbers are of the same type (be it int, long, or double), we will usually keep the same type in the output. There are some edge cases (e.g. when the summation of integers will not fit in an integer, we will return a long), but for the most part the types are preserved. If a $sum is processing a mix of types, it will use the 'widest' type as the output type. In this case, we see both doubles and Decimals, so we output the result as a Decimal since that is the more precise type. The same thing happens if you mix longs and doubles:

> db.foo.drop()
true
> db.foo.insert({x: NumberLong(1)})
WriteResult({ "nInserted" : 1 })
> db.foo.insert({x: NumberLong(1)})
WriteResult({ "nInserted" : 1 })
> db.foo.aggregate([{$group: {_id: null, sum: {$sum: "$x"}}}])
{ "_id" : null, "sum" : NumberLong(2) }
> db.foo.insert({x: 1})
WriteResult({ "nInserted" : 1 })
> db.foo.aggregate([{$group: {_id: null, sum: {$sum: "$x"}}}])
{ "_id" : null, "sum" : 3 }

Comment by Udo Held [ 28/Sep/16 ]

I wouldn't be surprised if its unrelated. Using only NumberDecimal works as expected. You may add it above ot the steps for reproduction.

db.fruitsnd.drop()
 
db.fruitsnd.insert( { price: NumberDecimal("6.95"), type : "sweet" } )
db.fruitsnd.insert( { price: NumberDecimal("7.49"), type : "sweet" } )
db.fruitsnd.insert( { price: NumberDecimal("2.99"), type : "sweet" } )
 
db.fruitsnd.aggregate([
  {
    $group: {
	  _id: "$type",
	  totalAmount: { $sum: "$price" },
	  count: { $sum: 1 }
	}
  }
])
 
{ "_id" : "sweet", "totalAmount" : NumberDecimal("17.43"), "count" : 3 }

Comment by Ramon Fernandez Marina [ 28/Sep/16 ]

Thanks for the bug report udoheld; the root cause for this behavior could be the same as for SERVER-26193, but we don't know yet – we'll post updates to this ticket when we know more.

Cheers,
Ramón.

Comment by Udo Held [ 27/Sep/16 ]

The problem shows up, for $avg as well. Probably it should be checked for all aggregation functions.

Generated at Thu Feb 08 04:11:53 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.