[SERVER-71557] The server does not always round half to even Created: 22/Nov/22 Updated: 27/Oct/23 Resolved: 09/Jan/23 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Query Execution |
| Affects Version/s: | 6.0.3 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Minor - P4 |
| Reporter: | Valentin Kavalenka | Assignee: | Ivan Fefer |
| Resolution: | Works as Designed | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||
| Assigned Teams: |
Query Execution
|
||||||||||||
| Operating System: | ALL | ||||||||||||
| Sprint: | QE 2022-12-26, QE 2023-01-09, QE 2023-01-23 | ||||||||||||
| Participants: | |||||||||||||
| Description |
|
The server documentation states "Rounding to Even Values. When rounding on a value of 5, $round rounds to the nearest even value." It's unclear how to interpret the "rounding on a value of 5" wording, but the only interpretation I can think of is that the server is supposed to round half to even, a.k.a. banker's rounding, which minimizes cumulative error when applied repeatedly over a sequence of calculations. However, it does not seem like the server consistently rounds half to even. [ok] Rounding to 0 decimal places and summing: [0.5, 1.5, 2.5, 3.5, 4.5, 5.5, 6.5, 7.5, 8.5, 9.5].
Both sum of exact and sum of rounded are 50, no summation error was accumulated, all is good. [ok] Rounding to 1 decimal places and summing: [0.05, 0.15, 0.25, 0.35, 0.45, 0.55, 0.65, 0.75, 0.85, 0.95].
There is still no accumulated summation error. [fail] Rounding to 2 decimal places and summing: [0.005, 0.015, 0.025, 0.035, 0.045, 0.055, 0.065, 0.075, 0.085, 0.095].
sum of rounded is no longer equal to sum of exact. The accumulated error of summation is 0.02. Here is how the results look like when half to even rounding is applied consistently (click here to open and run the below code in an online IDE):
|
| Comments |
| Comment by Ivan Fefer [ 09/Jan/23 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
This is normal behavior for floating point numbers. We created a follow-up ticket to make the documentation more clear about that. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Valentin Kavalenka [ 04/Jan/23 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Ah, right, Decimal128 has a very much limited width, unlike Java SE BigDecimal (unscaledValue * 10-scale), which may have virtually unlimited unscaled value with the scale being limited to i32. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Ivan Fefer [ 04/Jan/23 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
valentin.kovalenko@mongodb.com Also note that Decimal128 is the same floating point data format, just with more bits. So the same rounding issues will happen, but we need larger numbers to trigger them. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Valentin Kavalenka [ 04/Jan/23 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Thanks, ivan.fefer@mongodb.com, with explicit use of Decimal128 rounding to even works (see below). So this also seems like something to document in https://www.mongodb.com/docs/manual/reference/operator/aggregation/round/#rounding-to-even-values .
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Ivan Fefer [ 04/Jan/23 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
This is not a bug. MongoDB, JavaScript and most other programming languages and databases use 64-bit double precision floating point numbers (or "doubles" for short) to represent non-integer numbers. This format is defined by IEEE-754 standard. This format is inherently imprecise when dealing with decimal numbers.
Numbers like 0.05 are not exactly represented in this format, because they are either infinite or need much more digits in binary.
But integers and half-integers are represented precisely, so rounding them up to an integers works as indented:
Number used in
What I think we should do: 2. For $trunc doc we need to maybe add this example so people are not surprised. 3. Usually when people are worried about such things, it means that they are using doubles for calculations that require absolute precision (for example, working with currency). We should add a disclaimer about doubles and their precision and recommend to use Decimal128 for such applications. We already have an small article about it here: https://www.mongodb.com/developer/products/mongodb/bson-data-types-decimal128/
|