Wrong returned Values on simple SUM-Aggregation

Elastic Stack Elasticsearch
1

The expected Result is 149314.2, the returned wrong Result is 149314.203125

What could be the Problem?

This is my Query:
POST sap-lo-po/_search
{
"query": {
"match": {
"EBELN": "4500017596"
}
},
"aggs": {
"Bestellwert": {
"extended_stats": {
"field": "EKPO.NETWR"
}
}
},
"size": 0,
"_source": [
"EBELN.keyword",
"EKPO.NETWR"
]
}

This is what i get:
{
"took": 0,
"timed_out": false,
"_shards": {
"total": 1,
"successful": 1,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 1,
"max_score": 0,
"hits":
},
"aggregations": {
"Bestellwert": {
"count": 1,
"min": 149314.203125,
"max": 149314.203125,
"avg": 149314.203125,
"sum": 149314.203125,
"sum_of_squares": 22294731254.85376,
"variance": 0,
"std_deviation": 0,
"std_deviation_bounds": {
"upper": 149314.203125,
"lower": 149314.203125
}
}
}
}

2

The Document:

{
  "_index": "sap-lo-po",
  "_type": "doc",
  "_id": "4500017596",
  "_version": 1,
  "_score": null,
  "_source": {
    "EKPO": [
      {
        "DPTYP": "",
        "ABSKZ": "",
        "ADDRNUM": "",
        "ARSPS": 0,
        "ZWERT": 0,
        "LOEKZ": "",
        "VSART": "",
        "UMREN": 1,
        "MLMAA": "",
        "LBLKZ": "",
        "CONS_ORDER": "",
        "ABMNG": 0,
        "ABUEB": "",
        "SPE_CHNG_SYS": "",
        "ANZSN": 0,
        "DPAMT": 0,
        "GRANT_NBR": "",
        "DISUB_SOBKZ": "",
        "WEORA": "",
        "SPE_CRM_SO_ITEM": 0,
        "HANDOVERLOC": "",
        "KO_PARGB": "",
        "RESLO": "",
        "AGDAT": "0001-01-01",
        "DISUB_PSPNR": 0,
        "KNTTP": "",
        "EHTYP": "",
        "DPDAT": "0001-01-01",
        "KO_GSBER": "",
        "PRDAT": "2018-03-01",
        "CCOMP": "",
        "ZGTYP": "",
        "EMPST": "",
        "SOURCE_KEY": "",
        "KO_PPRCTR": "",
        "BUDGET_PD": "",
        "MEPRF": "",
        "/BEV1/NEDEPFREE": "",
        "NAVNW": 0,
        "PEINH": 1,
        "UNTTO": 0,
        "KTMNG": 0,
        "WEBAZ": 1,
        "ANFPS": 0,
        "UPVOR": "",
        "PRIO_URG": 0,
        "DISUB_KUNNR": "",
        "SPE_CRM_REF_ITEM": "",
        "BRGEW": 0,
        "MWSKZ": "",
        "CHG_FPLNR": "",
        "REASON_CODE": "",
        "VORAB": "",
        "MRPIND": "",
        "BSARK": "",
        "NOVET": "",
        "KO_PRCTR": "0000001000",
        "UEBTO": 0,
        "ARSNR": 0,
        "ABELP": 0,
        "KZTLF": "",
        "AFPNR": 0,
        "STATU": "",
        "SKTOF": "",
        "VOLUM": 0,
        "UEBPO": 0,
        "ABDAT": "0001-01-01",
        "ADVCODE": "",
        "SPE_CRM_SO": "",
        "/SAPMP/GPOSE": 0,
        "BNFPO": 0,
        "BSTAE": "",
        "LMEIN": "ST",
        "MTART": "ROH",
        "UMREZ": 1,
        "ITCONS": "",
        "LTSNR": "",
        "GNETWR": 0,
        "NRFHG": "",
        "ABELN": "",
        "UPTYP": "",
        "MFRNR": "",
        "INFNR": "5300000210",
        "SSQSS": "",
        "ANZPU": 0,
        "KBLNR": "",
        "ADPRI": "",
        "FLS_RSTO": "",
        "FISCAL_INCENTIVE_ID": "",
        "PRSDR": "X",
        "SPE_CRM_FKREL": "",
        "BSGRU": "",
        "DISUB_VBELN": "",
        "SPE_ABGRU": "",
        "XERSY": "",
        "MANDT": "200",
        "BWTAR": "",
        "EBONF": "X",
        "J_1BINDUST": "",
        "CHG_SRV": "",
        "INCO1": "",
        "KZWI4": 0,
        "DISUB_OWNER": "",
        "STPAC": "",
        "WEBRE": "",
        "ANGPN": 0,
        "TXJCD": "",
        "CUOBJ": 0,
        "ADMOI": "",
        "PUNEI": "",
        "AUDAT": "0001-01-01",
        "BRTWR": 153300,
        "WEPOS": "X",
        "FFZHI": 0,
        "MANUAL_TC_REASON": "",
        "SOURCE_ID": "",
        "PUT_BACK": "",
        "POL_ID": "",
        "BANFN": "",
        "LGBZO": "",
        "KZWI6": 0,
        "PACKNO": 0,
        "DPPCT": 0,
        "J_1AIDATEP": "0001-01-01",
        "BWTTY": "",
        "XOBLR": "X",
        "ADACN": "",
        "FKBER": "",
        "SPE_CQ_NOCQ": "",
        "MFRGR": "",
        "RETPC": 0,
        "KBLPOS": 0,
        "SPE_CRM_REF_SO": "",
        "KZWI1": 0,
        "SRV_BAS_COM": "",
        "LEBRE": "",
        "MAHN2": 20,
        "REVLV": "",
        "KOLIF": "",
        "BUKRS": "1000",
        "MAHNZ": 0,
        "RETPO": "",
        "EBELP": 10,
        "BPUMN": 1,
        "KANBA": "",
        "SRM_CONTRACT_ID": "",
        "MPROF": "",
        "FISCAL_INCENTIVE": "",
        "TZONRC": "CET",
        "NFABD": "0001-01-01",
        "AUREL": "",
        "NETPR": 49.77,
        "SPINF": "C",
        "LGORT": "",
        "DISUB_POSNR": 0,
        "REFSITE": "",
        "USEQU": "",
        "EGLKZ": "",
        "KZBWS": "",
        "BLK_REASON_TXT": "",
        "GEBER": "",
        "ATTYP": "",
        "EBON3": "",
        "IPRKZ": "",
        "SIKGR": "",
        "TXZ01": "HD GLAD BOY tank silver",
        "STAFO": "SAP",
        "TC_AUT_DET": "",
        "DRUNR": 1,
        "AEDAT": "2018-03-01",
        "REPOS": "X",
        "J_1BOWNPRO": "",
        "BONBA": 149314.2,
        "DIFF_INVOICE": "",
        "EXT_RFX_SYSTEM": "",
        "SATNR": "",
        "EXT_RFX_ITEM": "",
        "EXCPE": 0,
        "PNSTAT": "",
        "SERRU": "",
        "MHDRZ": 0,
        "IDNLF": "",
        "WABWE": "",
        "FABKZ": "",
        "WERKS": "1000",
        "BSTYP": "F",
        "MFRPN": "",
        "BLK_REASON_ID": "",
        "J_1BNBM": "",
        "J_1BMATUSE": "",
        "J_1AINDXP": "",
        "SPE_EWM_DTC": "",
        "LABNR": "",
        "VOLEH": "",
        "TWRKZ": "",
        "ANFNR": "",
        "SPE_INSMK_SRC": "",
        "AKTNR": "",
        "DRUHR": "00:00:00",
        "MAHN3": 30,
        "UMSOK": "",
        "EKKOL": "",
        "RDPRF": "",
        "LFRET": "",
        "/BEV1/NESTRUCCAT": "",
        "APOMS": "",
        "INCO2": "",
        "EBELN": "4500017596",
        "ADRN2": "",
        "KZKFG": "",
        "VRTKZ": "",
        "FIXMG": "",
        "ANGNR": "",
        "FPLNR": "",
        "J_1BMATORG": "",
        "NLABD": "0001-01-01",
        "TRMRISK_RELEVANT": "",
        "MFZHI": 0,
        "DRDAT": "0001-01-01",
        "NETWR": 149314.2,
        "BPUMZ": 1,
        "MATKL": "001",
        "ETFZ2": 0,
        "GEWEI": "",
        "SPE_CQ_CTRLTYPE": "",
        "EFFWR": 144834.77,
        "ADRNR": "",
        "SAISO": "",
        "EILDT": "0001-01-01",
        "LGBZO_B": "",
        "CONSNUM": 0,
        "BPRME": "ST",
        "KZWI5": 0,
        "TAX_SUBJECT_ST": "",
        "INSNC": "",
        "EXLIN": "",
        "NTGEW": 0,
        "KUNNR": ""
      }
    ]
}
3

Limits in floating point precision as well as rounding errors are unfortunately a thing. I would generally go for scaled_floats with a scaling factor of 100 here. To make this a little more clear let's look at a minimal example based on your data:

Mapping, sample document, query:

DELETE sap-lo-po

PUT sap-lo-po
{
  "settings": {
    "number_of_shards": 1
  }, 
  "mappings": {
    "_doc": {
      "properties": {
        "EKPO": {
          "properties": {
            "NETWR": {
              "type": "scaled_float",
              "scaling_factor": 100
            }
          }
        }
      }
    }
  }
}

PUT sap-lo-po/_doc/1
{
  "EBELN": "4500017596",
  "EKPO": [
    {
      "NETWR": 149314.2
    }
  ]
}

POST sap-lo-po/_search
{
  "query": {
    "match": {
      "EBELN": "4500017596"
    }
  },
  "aggs": {
    "Bestellwert": {
      "extended_stats": {
        "field": "EKPO.NETWR"
      }
    }
  },
  "size": 0,
  "_source": [
    "EBELN.keyword",
    "EKPO.NETWR"
  ]
}

Result:

{
  "took" : 40,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 1,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "aggregations" : {
    "Bestellwert" : {
      "count" : 1,
      "min" : 149314.2,
      "max" : 149314.2,
      "avg" : 149314.2,
      "sum" : 149314.2,
      "sum_of_squares" : 2.2294730321640003E10,
      "variance" : 0.0,
      "std_deviation" : 0.0,
      "std_deviation_bounds" : {
        "upper" : 149314.2,
        "lower" : 149314.2
      }
    }
  }
}

This looks better :slight_smile:

Though it won't solve all your problems. I hope it's good enough for money that will always have two digits after the comma, but see elasticsearch/issues/32570 for issues in more edge case situations.

1 Like
4

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