How do i get accurate sum in elasticsearch based on source hits?

Question

How do i get an exact sum aggregation in elasticsearch? Fore reference i am currently using elasticsearch 5.6 and the my index mapping looks like this:

{
  "my-index":{
    "mappings":{
      "my-type":{
        "properties":{
          "id":{
            "type":"keyword"
          },
          "fieldA":{
            "type":"double"
          },
          "fieldB":{
            "type":"double"
          },
          "fieldC":{
            "type":"double"
          },
          "version":{
            "type":"long"
          }
        }
      }
    }
  }
}

The search query generated (using java client) is:

{
 /// ... some filters here
 "aggregations" : {
       "fieldA" : {
         "sum" : {
           "field" : "fieldA"
         }
       },
       "fieldB" : {
         "sum" : {
           "field" : "fieldB"
         }
       },
       "fieldC" : {
         "sum" : {
           "field" : "fieldC"
         }
       }
     }
}

However my result hits generate the following:

{
    "took": 10,
    "timed_out": false,
    "_shards": {
        "total": 3,
        "successful": 3,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 5,
        "max_score": 3.8466966,
        "hits": [
            {
                "_index": "my-index",
                "_type": "my-type",
                "_id": "25a203b63e264fd2be13db006684b06d",
                "_score": 3.8466966,
                "_source": {
                    "fieldC": 108,
                    "fieldA": 108,
                    "fieldB": 0
                }
            },
            {
                "_index": "my-index",
                "_type": "my-type",
                "_id": "25a203b63e264fd2be13db006684b06d",
                "_score": 3.8466966,
                "_source": {
                    "fieldC": -36,
                    "fieldA": 108,
                    "fieldB": 144
                }
            },
            {
                "_index": "my-index",
                "_type": "my-type",
                "_id": "25a203b63e264fd2be13db006684b06d",
                "_score": 3.8466966,
                "_source": {
                    "fieldC": -7.2,
                    "fieldA": 1.8,
                    "fieldB": 9
                }
            },
            {
                "_index": "my-index",
                "_type": "my-type",
                "_id": "25a203b63e264fd2be13db006684b06d",
                "_score": 3.8466966,
                "_source": {
                    "fieldC": 14.85,
                    "fieldA": 18.9,
                    "fieldB": 4.05
                }
            },
            {
                "_index": "my-index",
                "_type": "my-type",
                "_id": "25a203b63e264fd2be13db006684b06d",
                "_score": 3.8466966,
                "_source": {
                    "fieldC": 36,
                    "fieldA": 36,
                    "fieldB": 0
                }
            }
        ]
    },
    "aggregations": {
        "fieldA": {
            "value": 272.70000000000005
        },
        "fieldB": {
            "value": 157.05
        },
        "fieldC": {
            "value": 115.64999999999999
        }
    }
}

why do i get:

115.64999999999999 instead of 115.65 in fieldC 272.70000000000005 instead of 272.7 in fieldA

should i use float instead of double? or is there a way i can change the query without using painless script and using java's BigDecimal with specified precision and rounding mode?

Answer 1

It has to do with float number precision in JavaScript (similar to what can be seen here and explained here).

Here are two ways to check this:

A. If you node.js installed, just type node at the prompt and then enter the sum of all fieldA values:

 $ node
 108 - 36 - 7.2 + 14.85 + 36
 115.64999999999999            <--- this is the answer

B. Open the Developer tools of your browser and pick the Console view. Then type the same sum as above:

 > 108-36-7.2+14.85+36
 < 115.64999999999999

As you can see, both results are consistent with what you're seeing in your ES response.

One way to circumvent this is to store your numbers either as normal integers (i.e. 1485 instead of 14.85, 3600 instead of 36, etc) or as scaled_float with a scaling factor of 100 (or bigger depending on the precision you need)