Loading...

XML

Word

Printable

JSON

Type: Bug
Resolution: Works as Designed
Priority: Minor - P4
Fix Version/s: None
Affects Version/s: 6.0.3
Component/s: Query Execution
Labels:
None

Assigned Teams:

Query Execution
Operating System:
ALL
Sprint:
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

SERVER-50541 Problem when the $trunc operator

Closed

DOCS-15806 [SERVER] Fix $round and $trunc docs to avoid stating that floating point input types are converted to integer output types

Backlog

Assignee:: Ivan Fefer

Reporter:: Valentin Kavalenka

Participants:: Ivan Fefer, Valentin Kavalenka

Votes:: 0 Vote for this issue

Watchers:: 10 Start watching this issue

Created:: Nov 22 2022 08:29:56 PM UTC

Updated:: Oct 27 2023 01:51:49 PM UTC

Resolved:: Jan 09 2023 03:00:59 PM UTC

Details

Description

[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].

[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].

[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].

Attachments

Issue Links

Activity

People

Dates

[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]`.

[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]`.

[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]`.