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
}