-
Type:
Bug
-
Resolution: Works as Designed
-
Priority:
Minor - P4
-
None
-
Affects Version/s: 6.0.3
-
Component/s: Query Execution
-
None
-
Query Execution
-
ALL
-
QE 2022-12-26, QE 2023-01-09, QE 2023-01-23
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].
db.aggregate([
{"$documents": [
{"exact": [0.5, 1.5, 2.5, 3.5, 4.5, 5.5, 6.5, 7.5, 8.5, 9.5]}
]},
{"$project": {
"exact": true,
"rounded": {"$map": {
"input": "$exact",
"in": {"$round": ["$$this", 0]}
}}
}},
{"$project": {
"exact": true,
"sum of exact": {"$reduce": {
"input": "$exact",
"initialValue": 0,
"in": {"$add": ["$$value", "$$this"]}
}},
"rounded": true,
"sum of rounded": {"$reduce": {
"input": "$rounded",
"initialValue": 0,
"in": {"$add": ["$$value", "$$this"]}
}}
}},
{"$project": {
"exact": true,
"sum of exact": true,
"rounded sum of exact": {"$round": ["$sum of exact", 0]},
"rounded": true,
"sum of rounded": true
}}
])
[
{
exact: [
0.5, 1.5, 2.5, 3.5,
4.5, 5.5, 6.5, 7.5,
8.5, 9.5
],
rounded: [
0, 2, 2, 4, 4,
6, 6, 8, 8, 10
],
'sum of exact': 50,
'sum of rounded': 50,
'rounded sum of exact': 50
}
]
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].
db.aggregate([
{"$documents": [
{"exact": [0.05, 0.15, 0.25, 0.35, 0.45, 0.55, 0.65, 0.75, 0.85, 0.95]}
]},
{"$project": {
"exact": true,
"rounded": {"$map": {
"input": "$exact",
"in": {"$round": ["$$this", 1]}
}}
}},
{"$project": {
"exact": true,
"sum of exact": {"$reduce": {
"input": "$exact",
"initialValue": 0,
"in": {"$add": ["$$value", "$$this"]}
}},
"rounded": true,
"sum of rounded": {"$reduce": {
"input": "$rounded",
"initialValue": 0,
"in": {"$add": ["$$value", "$$this"]}
}}
}},
{"$project": {
"exact": true,
"sum of exact": true,
"rounded sum of exact": {"$round": ["$sum of exact", 1]},
"rounded": true,
"sum of rounded": true
}}
])
[
{
exact: [
0.05, 0.15, 0.25,
0.35, 0.45, 0.55,
0.65, 0.75, 0.85,
0.95
],
rounded: [
0.1, 0.1, 0.2, 0.3,
0.5, 0.6, 0.7, 0.8,
0.8, 0.9
],
'sum of exact': 5,
'sum of rounded': 5,
'rounded sum of exact': 5
}
]
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].
db.aggregate([
{"$documents": [
{"exact": [0.005, 0.015, 0.025, 0.035, 0.045, 0.055, 0.065, 0.075, 0.085, 0.095]}
]},
{"$project": {
"exact": true,
"rounded": {"$map": {
"input": "$exact",
"in": {"$round": ["$$this", 2]}
}}
}},
{"$project": {
"exact": true,
"sum of exact": {"$reduce": {
"input": "$exact",
"initialValue": 0,
"in": {"$add": ["$$value", "$$this"]}
}},
"rounded": true,
"sum of rounded": {"$reduce": {
"input": "$rounded",
"initialValue": 0,
"in": {"$add": ["$$value", "$$this"]}
}}
}},
{"$project": {
"exact": true,
"sum of exact": true,
"rounded sum of exact": {"$round": ["$sum of exact", 2]},
"rounded": true,
"sum of rounded": true
}}
])
[
{
exact: [
0.005, 0.015, 0.025,
0.035, 0.045, 0.055,
0.065, 0.075, 0.085,
0.095
],
rounded: [
0.01, 0.01, 0.03,
0.04, 0.04, 0.06,
0.07, 0.07, 0.09,
0.1
],
'sum of exact': 0.5,
'sum of rounded': 0.52,
'rounded sum of exact': 0.5
}
]
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):
import java.math.BigDecimal; import java.math.RoundingMode; import java.util.List; import java.util.stream.Collectors; import java.util.stream.Stream; class Main { public static void main(String... args) { int place = 2; RoundingMode mode = RoundingMode.HALF_EVEN; List<BigDecimal> exact = Stream.of("0.005", "0.015", "0.025", "0.035", "0.045", "0.055", "0.065", "0.075", "0.085", "0.095") .map(BigDecimal::new).collect(Collectors.toList()); BigDecimal sumOfExact = exact.stream().reduce(BigDecimal.ZERO, BigDecimal::add); BigDecimal roundedSumOfExact = sumOfExact.setScale(place, mode); List<BigDecimal> rounded = exact.stream().map(v -> v.setScale(place, mode)).collect(Collectors.toList()); BigDecimal sumOfRounded = rounded.stream().reduce(BigDecimal.ZERO, BigDecimal::add); System.out.printf("exact: %s" + "\nsum of exact: %s" + "\nrounded sum of exact: %s" + "\nrounded: %s" + "\nsum of rounded: %s\n", exact, sumOfExact, roundedSumOfExact.toPlainString(), rounded, sumOfRounded.toPlainString()); } }
exact: [0.005, 0.015, 0.025, 0.035, 0.045, 0.055, 0.065, 0.075, 0.085, 0.095] sum of exact: 0.500 rounded sum of exact: 0.50 rounded: [0.00, 0.02, 0.02, 0.04, 0.04, 0.06, 0.06, 0.08, 0.08, 0.10] sum of rounded: 0.50
- is related to
-
-
- Closed
-