[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:
Related
is related to SERVER-50541 Problem when the $trunc operator Closed
is related to DOCS-15806 [SERVER] Fix $round and $trunc docs t... Backlog
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].

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



 Comments   
Comment by Ivan Fefer [ 09/Jan/23 ]

This is normal behavior for floating point numbers.
If you want more precision, consider using Decimal128 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 .

db.aggregate([
        {"$documents": [
            {"exact": [NumberDecimal("0.005"), NumberDecimal("0.015"), NumberDecimal("0.025"), NumberDecimal("0.035"), NumberDecimal("0.045"), NumberDecimal("0.055"), NumberDecimal("0.065"), NumberDecimal("0.075"), NumberDecimal("0.085"), NumberDecimal("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: [
      Decimal128("0.005"),
      Decimal128("0.015"),
      Decimal128("0.025"),
      Decimal128("0.035"),
      Decimal128("0.045"),
      Decimal128("0.055"),
      Decimal128("0.065"),
      Decimal128("0.075"),
      Decimal128("0.085"),
      Decimal128("0.095")
    ],
    rounded: [
      Decimal128("0.00"),
      Decimal128("0.02"),
      Decimal128("0.02"),
      Decimal128("0.04"),
      Decimal128("0.04"),
      Decimal128("0.06"),
      Decimal128("0.06"),
      Decimal128("0.08"),
      Decimal128("0.08"),
      Decimal128("0.10")
    ],
    'sum of exact': Decimal128("0.500"),
    'sum of rounded': Decimal128("0.50"),
    'rounded sum of exact': Decimal128("0.50")
  }
]

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.
For example, Number. EPSILON property represents the difference between 1 and the smallest floating point number greater than 1. 

Number.EPSILON
2.220446049250313e-16 

Numbers like 0.05 are not exactly represented in this format, because they are either infinite or need much more digits in binary.
For example, if we use toFixed(20) method to print 0.05 up to 20 digits, we will see that it is actually slightly more than 0.05, so rounding it to 0.1 is "correct".

0.05.toFixed(20)
'0.05000000000000000278' 

But integers and half-integers are represented precisely, so rounding them up to an integers works as indented:

0.5.toFixed(20)
'0.50000000000000000000'  

 

Number used in SERVER-50541 to demonstrate $trunc operator "bug" is 4.56 is actually slightly less than 4.56, so it is truncated to 4.55:

4.56.toFixed(20)
'4.55999999999999960920' 

 

What I think we should do:
1. Update $round doc on rounding to even to make state clearly that it only applies when rounding to an integer. https://www.mongodb.com/docs/manual/reference/operator/aggregation/round/#rounding-to-even-values 

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/

 

Generated at Thu Feb 08 06:19:20 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.