Strange rounding issue when aggregating as sum

[konsultaner]

my environment running ArangoDB

I'm using the latest ArangoDB of the respective release series:
3.1 on Single-Server with mmfiles on Windows 10

I'm issuing AQL via:

• web interface with this browser: running on this OS:
• arangosh
• this Driver: Java latest

I have the following result from my query:

[
  {
    "bearer": "Client/7163944",
    "grossPrice": 23.529999999999998,
    "taxes": null,
    "netPrice": 21.990654205607473,
    "accountingFrom": "2017-02-01T00:00:00.000Z",
    "accountingTo": "2017-02-28T00:00:00.000Z",
    "invoiceItems": [
      {
        "bearer": "Client/7163944",
        "deliveryDate": "2017-02-23T00:00:00.000Z",
        "mealName": "MenuA",
        "dishName": "Pancakes",
        "articleDescription": "Pancakes with jam",
        "amount": 1,
        "grossPrice": 3.55,
        "originalGrossPrice": 3.55,
        "taxRate": 0.07
      },
      {
        "bearer": "Client/7163944",
        "deliveryDate": "2017-02-22T00:00:00.000Z",
        "mealName": "MenuB",
        "dishName": "Potatoes with cottage cheese",
        "articleDescription": null,
        "amount": 3,
        "grossPrice": 3.11,
        "originalGrossPrice": 3.11,
        "taxRate": 0.07
      },
      {
        "bearer": "Client/7163944",
        "deliveryDate": "2017-02-23T00:00:00.000Z",
        "mealName": "MenuA",
        "dishName": "Pancakes",
        "articleDescription": "Pancakes with jam",
        "amount": 2,
        "grossPrice": 3.55,
        "originalGrossPrice": 3.55,
        "taxRate": 0.07
      },
      {
        "bearer": "Client/7163944",
        "deliveryDate": "2017-02-22T00:00:00.000Z",
        "mealName": "MenuA",
        "dishName": "Spaghetti",
        "articleDescription": "Spaghetti with tomato sauce",
        "amount": 1,
        "grossPrice": 3.55,
        "originalGrossPrice": 3.55,
        "taxRate": 0.07
      }
    ],
    "invoiceNumber": null,
    "generalLedgerAccountId": "GeneralLedgerAccount/7164102",
    "debitor": null,
    "creditor": null,
    "creditorBankAccount": null
  }
]

The grossPrice is am aggregation using this:

grossPrice:SUM(invoiceItems[* RETURN CURRENT.invoiceItem.grossPrice * CURRENT.invoiceItem.amount])

I expected to get 23.53 instead I got 23.529999999999998. I thought this should not happen using double precision as explained in the docs? Even without the multiplication I get a strange value.

[jsteemann]

Seems to be a floating-point rounding issue.
With an 8 byte double-precision type, the result returned is somewhat expected, as not all numbers can be represented exactly.
That is a general issue when using floating point numbers, not limited to ArangoDB.
I tried several other environments (node/v8, python, php, c++) to check what result they will produce, and they do come up with similar results:

$ node
> 1.0 * 3.55 + 3.0 * 3.11 + 2.0 * 3.55 + 1.0 * 3.55
23.529999999999998

or

$ python
Python 2.7.13 (default, Jan 19 2017, 14:48:08) 
[GCC 6.3.0 20170118] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> 1.0 * 3.55 + 3.0 * 3.11 + 2.0 * 3.55 + 1.0 * 3.55
23.529999999999998

or

$ irb
irb(main):001:0> 1.0 * 3.55 + 3.0 * 3.11 + 2.0 * 3.55 + 1.0 * 3.55
=> 23.529999999999998

or

$ php -r "ini_set('precision', 20); print 1.0 * 3.55 + 3.0 * 3.11 + 2.0 * 3.55 + 1.0 * 3.55;"
23.529999999999997584

or

$ cat dbl.cpp
#include <iostream>
#include <iomanip>
#include <array>

int main() {
  std::array<double, 4> amounts = { 1.0, 3.0, 2.0, 1.0 };
  std::array<double, 4> grossPrices = { 3.55, 3.11, 3.55, 3.55 };

  double total = 0.0;

  for (size_t i = 0; i < amounts.size(); ++i) {
    total += amounts[i] * grossPrices[i];
  }

  std::cout << "total: " << std::setprecision(20) << total << std::endl;
}
$ g++ -std=c++11 dbl.cpp && ./a.out 
total: 23.529999999999997584

If some implementations return the "expected" result with two decimal digits, then they probably have a smarter procedure for rounding floating point values for display/return purposes.

[konsultaner]

@jsteemann I thought arango would use double precision in general for its queries? See the docs: All numeric values are treated as 64-bit double-precision values internally

[jsteemann]

Yes, and that is the source of the problem. Double-precision floating point values cannot safely represent every possible value without loss.
You probably know that classic example:

$ node
> 0.1 + 0.2 == 0.3
false

[konsultaner]

ok, I thought this would only happen with normal floats, because Javas double type handled this calculation just as expected. I helped myself with rounding it to the 6th decimal.

[jsteemann]

I think Java will use the same double-precision implementation under the hood, but potentially its rounding and/or display procedure for doubles is a bit different (read: smarter).