ESQL avg() stats leads to arithmetic overflow exception on large data

In the nyc_taxis dataset used in benchmarking, I’m getting an arithmetic exception when calculating avg of many longs.

The query:

from nyc_taxis
| eval epoch = to_long(dropoff_datetime)
| stats ae=avg(epoch), count(epoch)
| eval at=to_datetime(ae)

And the result:

{
    "error": {
        "root_cause": [
            {
                "type": "arithmetic_exception",
                "reason": "long overflow"
            }
        ],
        "type": "arithmetic_exception",
        "reason": "long overflow"
    },
    "status": 500
}

In ESQL arithmetic operations can lead to numerical overflows, and the principle is to return null and add a warning. We could do that in this case. However, since the avg function will return a double anyway, we could cast the longs to doubles up-front. Right now avg() is implemented as a sum()/count() and the division will return a double, we could instead change this to sum(to_double())/count() and get the conversion to double done one step earlier.

As a test, the following workaround works:

from nyc_taxis
| eval epoch = to_double(dropoff_datetime)
| stats ae=avg(epoch), count(epoch)
| eval at=to_datetime(ae)

{
    "columns": [
        {
            "name": "ae",
            "type": "double"
        },
        {
            "name": "count(epoch)",
            "type": "long"
        },
        {
            "name": "at",
            "type": "date"
        }
    ],
    "values": [
        [
            1431759064681.463,
            8320000,
            "2015-05-16T06:51:04.681Z"
        ]
    ]
}

So, three options here:

Implement the conversion to null and warnings for aggregations that have numerical overflow
Add an implicit cast to double avg() -> sum(to_double())/count()
Support big integers on sum(), requiring support for an additional type (big integer)

Pinging @elastic/es-ql (Team:QL)

Pinging @elastic/elasticsearch-esql (:Query Languages/ES|QL)

Pinging @elastic/es-analytics-geo (Team:Analytics)

Pinging @elastic/es-analytical-engine (Team:Analytics)

@alex-spies

@alex-spies Raised some questions about if we should always cast to a double before summing. Doing it isn't terribly hard, although it does create some headaches in the optimizer tests, but I agree it isn't clear that it's the best way forward. I've also opened #110437 to capture the 500 error on overflow in aggregations, which we should fix regardless of what we do here.

I think it's worth discussing if we want to have this cast or not, and if we decide we don't, close this in favor of #110437. I'm labeling this team-discuss to note that.

another option: don't use a surrogate, but instead have an intermediate state:

        @IntermediateState(name = "mean", type = "DOUBLE"),
        @IntermediateState(name = "count", type = "LONG")

comparable to StdDev.

ESQL: Avoid long overflows on AVG by casting to double (#148519)

ESQL: Avoid long overflows on AVG by casting to double (#148519) (#15…

Navigation Menu

Repository navigation

ESQL avg() stats leads to arithmetic overflow exception on large data #99575

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

ESQL avg() stats leads to arithmetic overflow exception on large data #99575

Description

Activity

elasticsearchmachine commented on Sep 14, 2023

elasticsearchmachine commented on Sep 14, 2023

elasticsearchmachine commented on Jan 2, 2024

elasticsearchmachine commented on Jul 2, 2024

not-napoleon commented on Jul 3, 2024

jan-elastic commented on Oct 16, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions