[BUG] can't evaluate on aggregator: sum

In the AWS OpenSearch, I have enabled the SQL plugin to use the SQL queries.

{
  "transient" : {
    "plugins.sql.enabled" : true
  }
}

I'm using the following query:

select `revenue` as `status`, sum(`_ingest.totalPrice`) as value, count(*) as count, date_format(receiptDate, 'y-MM') as aggDate, 'revenue' as type from revenues WHERE `_uid`='1234' and ((millis>=1706745600000 and millis<=1717199999999) or millis=-1) GROUP by `status`, aggDate

The above query is working fine and giving the correct results but sometimes the query failed with the following response:

{
  "error": {
    "details": "can't evaluate on aggregator: sum",
    "reason": "Invalid SQL query",
    "type": "ExpressionEvaluationException"
  },
  "status": 400
}

I have asked this already on SO about an years ago https://stackoverflow.com/questions/78556181/aws-opensearch-cant-evaluate-on-aggregator-sum

Start by reproducing the supplied SQL query through the AWS OpenSearch SQL plugin, comparing successful and failing executions of the sum aggregation. No source files or tests are named; trace the aggregation evaluation path for the reported "can't evaluate on aggregator: sum" response, and consider the issue done when the intermittent failure is understood and covered by a regression test.