[BUG] Larger integers in a SUM aggregation are ignored

[acarbonetto]

What is the bug?
When summing integers, large integers can be ignored.

How can one reproduce the bug?
Steps to reproduce the behavior:

SELECT SUM(value) FROM bigint;
SUM(value)
500

SELECT value FROM bigint;
[
            -9223372026854775808
],
[
            9223372026854775807
],
[
            1000
],
[
            -500
]

What is the expected behavior?
Expected the sum to be: 499.
If you leave out the 1000 and -500 values, you get a result of 0.

What is your host/environment?
n/a

Do you have any screenshots?
n/a

Do you have any additional context?
n/a

[acarbonetto]

Note: in the example above, the mapping for value is a long.

According to https://opensearch.org/docs/2.0/opensearch/supported-field-types/numeric/, longs are supported up to 2^63.

Seems like aggregation probably converts the numbers to doubles to do the aggregation, since the response is:

    "aggregations": {
        "sum(value)": {
            "value": 500.0
        }
    }

And a conversion from long to double would result in the loss of the -1 sum. Also, this appears to be an issue on the OpenSearch aggregation side, NOT on the sql plugin side.

[acarbonetto]

Option A) Report an error when trying to aggregate on long values.
Option B) ask OpenSearch to support aggregation on long values as per their documentation: https://opensearch.org/docs/2.0/opensearch/supported-field-types/numeric/
Option C) compute these aggregations in-memory (sum on a field defined as a long).

Regardless, we should fail with an error for any number larger than what is supported by OpenSearch (https://opensearch.org/docs/2.0/opensearch/supported-field-types/numeric).

[MitchellGale]

Option B was taken. Issue made in OpenSearch project opensearch-project/OpenSearch#5537.