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
}

Not sure why the query is failing. Any idea or workaround to resolve this?