[BUG] Aggregate on date function does not work as expected

[penghuo]

What is the bug?

PUT {{baseUrl}}/test_00001/
Content-Type: application/json

{
  "settings": {
    "index": {
      "number_of_shards": 1,
      "number_of_replicas": 0
    }
  },
  "mappings": {
    "properties": {
      "date": {
        "type": "keyword"
      },
      "status": {
        "type": "integer"
      }   
    }
  }
}

curl -XPOST "http://localhost:9200/_bulk?refresh=true" -H 'Content-Type: application/json' -d'
{ "index" : { "_index" : "test_00001", "_id" : "1" } }
{ "date" : "2022-01-01", "status" : 1}
{ "index" : { "_index" : "test_00001", "_id" : "2" } }
{ "date" : "2022-02-01", "status" : 2}
{ "index" : { "_index" : "test_00001", "_id" : "3" } }
{ "date" : "2022-03-01", "status" : 3}
{ "index" : { "_index" : "test_00001", "_id" : "4" } }
{ "date" : "2022-04-01", "status" : 2}
'

POST {{baseUrl}}/_plugins/_sql
Content-Type: application/json

{
  "query": "SELECT min(date(date)) FROM test_00001 GROUP BY status"
}

{
  "error": {
    "type": "SearchPhaseExecutionException",
    "reason": "Error occurred in OpenSearch engine: all shards failed",
    "details": "Shard[0]: AggregationExecutionException[Unsupported script value [2022-01-01], expected a number, date, or boolean]\n\nFor more details, please send request for Json format to see the raw response from OpenSearch engine."
  },
  "status": 503
}

[penghuo]

ScriptAggregate works on number/date/boolean data type. ExprDateValue return string which is not expected.

[dai-chen]

@Yury-Fridlyand just to confirm can we close this now?

[Yury-Fridlyand]

No, the fix is in #1061, it is not merged yet.