Sum aggregation precision

Hello,

I have a problem with sum fields with 3 decimal places. The Sum of all values is zero, but elastic sum aggregations return a very small number(like -2.837623469531536E-10).
I have created a test index and put the same values into scaled_float and double fields.

PUT my_index
{
  "mappings": {
    "dynamic": "strict",
    "properties": {
      "price": {
        "type": "scaled_float",
        "scaling_factor": 1000.
      },
      "decimal": {
        "type": "double"
      }
    }
  }
}

The proper result is returned only for scripted metric aggregation, which sum BigDecimal values of the decimal field.
Cast all values into Big decimal may cause performance problems. It is strange that scaled_float loses precision.
I am looking for a better solution than scripted metric aggregation, any ideas?

Best,
David

Query (elasticsearch 7.11, ale operations performed via Kibana):

  GET my_index/_search?request_cache=false
    {
      "size": 0,
      "aggregations": {
        "sum_scaled_float": {
          "sum": {
            "field": "price"
          }
        },
        "sum_decimal": {
          "sum": {
            "field": "decimal"
          }
        },
        "scripted_metric_sum_decimal": {
          "scripted_metric": {
            "init_script": {
              "source": "state.values = []",
              "lang": "painless"
            },
            "map_script": {
              "source": "state.values.add(BigDecimal.valueOf(doc.decimal.value))",
              "lang": "painless"
            },
            "combine_script": {
              "source": "BigDecimal value = BigDecimal.ZERO; for (a in state.values) { value = value.add(a) } return value",
              "lang": "painless"
            },
            "reduce_script": {
              "source": "BigDecimal value = BigDecimal.ZERO; for (a in states) { value = value.add(a) } return value",
              "lang": "painless"
            }
          }
        },
        "scripted_metric_scaled_float": {
          "scripted_metric": {
            "init_script": {
              "source": "state.values = []",
              "lang": "painless"
            },
            "map_script": {
              "source": "state.values.add(BigDecimal.valueOf(doc.price.value))",
              "lang": "painless"
            },
            "combine_script": {
              "source": "BigDecimal value = BigDecimal.ZERO; for (a in state.values) { value = value.add(a) } return value",
              "lang": "painless"
            },
            "reduce_script": {
              "source": "BigDecimal value = BigDecimal.ZERO; for (a in states) { value = value.add(a) } return value",
              "lang": "painless"
            }
          }
        }
      }
    }

Response:

    {
      "took" : 0,
      "timed_out" : false,
      "_shards" : {
        "total" : 1,
        "successful" : 1,
        "skipped" : 0,
        "failed" : 0
      },
      "hits" : {
        "total" : {
          "value" : 60,
          "relation" : "eq"
        },
        "max_score" : null,
        "hits" : [ ]
      },
      "aggregations" : {
        "sum_decimal" : {
          "value" : -2.9103830456733704E-10
        },
        "sum_scaled_float" : {
          "value" : -2.837623469531536E-10
        },
        "scripted_metric_scaled_float" : {
          "value" : 2.5E-12
        },
        "scripted_metric_sum_decimal" : {
          "value" : 0.000
        }
      }
    }

DATA :

    POST my_index/_doc/1
    {
    "price" : 6845.828,
    "decimal" : 6845.828
    }

    POST my_index/_doc/2
    {
    "price" : 47298.449,
    "decimal" : 47298.449
    }

    POST my_index/_doc/3
    {
    "price" : 17425.738,
    "decimal" : 17425.738
    }

    POST my_index/_doc/4
    {
    "price" : 224045.261,
    "decimal" : 224045.261
    }

    POST my_index/_doc/5
    {
    "price" : 53795.445,
    "decimal" : 53795.445
    }

    POST my_index/_doc/6
    {
    "price" : 42942.009,
    "decimal" : 42942.009
    }

    POST my_index/_doc/7
    {
    "price" : -115756.729,
    "decimal" : -115756.729
    }

    POST my_index/_doc/8
    {
    "price" : -28939.185,
    "decimal" : -28939.185
    }

    POST my_index/_doc/9
    {
    "price" : -42615.034,
    "decimal" : -42615.034
    }

    POST my_index/_doc/10
    {
    "price" : 746817.544,
    "decimal" : 746817.544
    }

    POST my_index/_doc/11
    {
    "price" : 93352.193,
    "decimal" : 93352.193
    }

    POST my_index/_doc/12
    {
    "price" : -3925718.071,
    "decimal" : -3925718.071
    }

    POST my_index/_doc/13
    {
    "price" : -981429.537,
    "decimal" : -981429.537
    }

    POST my_index/_doc/14
    {
    "price" : 10891.089,
    "decimal" : 10891.089
    }

    POST my_index/_doc/15
    {
    "price" : 410749.647,
    "decimal" : 410749.647
    }

    POST my_index/_doc/16
    {
    "price" : 10105.062,
    "decimal" : 10105.062
    }

    POST my_index/_doc/17
    {
    "price" : 2644.983,
    "decimal" : 2644.983
    }

    POST my_index/_doc/18
    {
    "price" : 660.0,
    "decimal" : 660.0
    }

    POST my_index/_doc/19
    {
    "price" : 37288.152,
    "decimal" : 37288.152
    }

    POST my_index/_doc/20
    {
    "price" : 23649.219,
    "decimal" : 23649.219
    }

    POST my_index/_doc/21
    {
    "price" : 102687.409,
    "decimal" : 102687.409
    }

    POST my_index/_doc/22
    {
    "price" : 5289.955,
    "decimal" : 5289.955
    }

    POST my_index/_doc/23
    {
    "price" : 186704.386,
    "decimal" : 186704.386
    }

    POST my_index/_doc/24
    {
    "price" : 18670.443,
    "decimal" : 18670.443
    }

    POST my_index/_doc/25
    {
    "price" : 2800.567,
    "decimal" : 2800.567
    }

    POST my_index/_doc/26
    {
    "price" : 23650.0,
    "decimal" : 23650.0
    }

    POST my_index/_doc/27
    {
    "price" : 2640.0,
    "decimal" : 2640.0
    }

    POST my_index/_doc/28
    {
    "price" : 311173.984,
    "decimal" : 311173.984
    }

    POST my_index/_doc/29
    {
    "price" : 65649.419,
    "decimal" : 65649.419
    }

    POST my_index/_doc/30
    {
    "price" : -10653.753,
    "decimal" : -10653.753
    }

    POST my_index/_doc/31
    {
    "price" : 102687.409,
    "decimal" : 102687.409
    }

    POST my_index/_doc/32
    {
    "price" : 215181.78,
    "decimal" : 215181.78
    }

    POST my_index/_doc/33
    {
    "price" : 68458.269,
    "decimal" : 68458.269
    }

    POST my_index/_doc/34
    {
    "price" : 74681.75,
    "decimal" : 74681.75
    }

    POST my_index/_doc/35
    {
    "price" : 11202.268,
    "decimal" : 11202.268
    }

    POST my_index/_doc/36
    {
    "price" : 2800.567,
    "decimal" : 2800.567
    }

    POST my_index/_doc/37
    {
    "price" : 410749.647,
    "decimal" : 410749.647
    }

    POST my_index/_doc/38
    {
    "price" : 585007.071,
    "decimal" : 585007.071
    }

    POST my_index/_doc/39
    {
    "price" : 24893.913,
    "decimal" : 24893.913
    }

    POST my_index/_doc/40
    {
    "price" : 94600.0,
    "decimal" : 94600.0
    }

    POST my_index/_doc/41
    {
    "price" : 11202.268,
    "decimal" : 11202.268
    }

    POST my_index/_doc/42
    {
    "price" : 16412.352,
    "decimal" : 16412.352
    }

    POST my_index/_doc/43
    {
    "price" : 171768.036,
    "decimal" : 171768.036
    }

    POST my_index/_doc/44
    {
    "price" : 10579.91,
    "decimal" : 10579.91
    }

    POST my_index/_doc/45
    {
    "price" : 94596.887,
    "decimal" : 94596.887
    }

    POST my_index/_doc/46
    {
    "price" : 17114.57,
    "decimal" : 17114.57
    }

    POST my_index/_doc/47
    {
    "price" : 23338.051,
    "decimal" : 23338.051
    }

    POST my_index/_doc/48
    {
    "price" : 6223.481,
    "decimal" : 6223.481
    }

    POST my_index/_doc/49
    {
    "price" : 4356.440,
    "decimal" : 4356.440
    }

    POST my_index/_doc/50
    {
    "price" : 2526.271,
    "decimal" : 2526.271
    }

    POST my_index/_doc/51
    {
    "price" : 56011.318,
    "decimal" : 56011.318
    }

    POST my_index/_doc/52
    {
    "price" : 35162.655,
    "decimal" : 35162.655
    }

    POST my_index/_doc/53
    {
    "price" : 43564.356,
    "decimal" : 43564.356
    }

    POST my_index/_doc/54
    {
    "price" : 140650.631,
    "decimal" : 140650.631
    }

    POST my_index/_doc/55
    {
    "price" : 27383.312,
    "decimal" : 27383.312
    }

    POST my_index/_doc/56
    {
    "price" : 149152.608,
    "decimal" : 149152.608
    }

    POST my_index/_doc/57
    {
    "price" : 77793.496,
    "decimal" : 77793.496
    }

    POST my_index/_doc/58
    {
    "price" : 146251.765,
    "decimal" : 146251.765
    }

    POST my_index/_doc/59
    {
    "price" : 11824.615,
    "decimal" : 11824.615
    }

    POST my_index/_doc/60
    {
    "price" : 21159.831,
    "decimal" : 21159.831
    }

The sum aggregation uses a kahan summation on double precision floating point values. I expect loss of precision, even if you are summing scaled floats.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.