[BUG] SQL query doesn't support different date formats without date casting

[rupal-bq]

What is the bug? Getting below error for user defined date format

"{\n  \"error\": {\n    \"reason\": \"Invalid SQL query\",\n    \"details\": \"date:03-Jan-24 in unsupported format, please use 'yyyy-MM-dd'\",\n    \"type\": \"SemanticCheckException\"\n  },\n  \"status\": 400\n}"

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

PUT testindex
{
  "mappings" : {
    "properties" :  {
      "release_date" : {
        "type" : "date",
        "format" : "dd-MMM-yy"
      }
    }
  }
}

PUT testindex/_doc/21 
{
  "release_date" : "03-Jan-24"
}

POST _plugins/_sql
{
"query":"select release_date from testindex where release_date='03-Jan-24'"
}

What is the expected behavior? Query should work without date casting

What is your host/environment?

• OS: [e.g. iOS]
• Version [e.g. 22]
• Plugins

Do you have any screenshots? If applicable, add screenshots to help explain your problem.

Do you have any additional context? Add any other context about the problem.

[manasvinibs]

We have one more similar issue reported from users when date in timestamp format in the SQL query is erroring out due to mismatch in the timestamp format between field mapping and the request query -

Sample query:

select count(*) from ami where State = 'available' and  Public = 'true' and CallerVersion = "2016-11-15 00:00:00";

select count(*) from ami where State = 'available' and  Public = 'true' and CallerVersion < "2016-11-15 00:00:00";

Error -

[INFO] 10f0cef3-4159-4c39-84e1-f4ba8b88be97 (Bobcat-29) com.amazonaws.fleetinsightsopensearchcommons.helper.ResponseConvertor: Original SQL response : {
  "error": {
    "reason": "Error occurred in OpenSearch engine: all shards failed",
    "details": "Shard[0]: OpenSearchParseException[failed to parse date field [2016-11-15 00:00:00] with format [strict_date_optional_time||epoch_millis]: [failed to parse date field [2016-11-15 00:00:00] with format [strict_date_optional_time||epoch_millis]]]; nested: IllegalArgumentException[failed to parse date field [2016-11-15 00:00:00] with format [strict_date_optional_time||epoch_millis]]; nested: NotSerializableExceptionWrapper[date_time_parse_exception: Failed to parse with all enclosed parsers];\nShard[1]: OpenSearchParseException[failed to parse date field [2016-11-15 00:00:00] with format [strict_date_optional_time||epoch_millis]: [failed to parse date field [2016-11-15 00:00:00] with format [strict_date_optional_time||epoch_millis]]]; nested: IllegalArgumentException[failed to parse date field [2016-11-15 00:00:00] with format [strict_date_optional_time||epoch_millis]]; nested: NotSerializableExceptionWrapper[date_time_parse_exception: Failed to parse with all enclosed parsers];\nShard[2]: OpenSearchParseException[failed to parse date field [2016-11-15 00:00:00] with format [strict_date_optional_time||epoch_millis]: [failed to parse date field [2016-11-15 00:00:00] with format [strict_date_optional_time||epoch_millis]]]; nested: IllegalArgumentException[failed to parse date field [2016-11-15 00:00:00] with format [strict_date_optional_time||epoch_millis]]; nested: NotSerializableExceptionWrapper[date_time_parse_exception: Failed to parse with all enclosed parsers];\nShard[3]: OpenSearchParseException[failed to parse date field [2016-11-15 00:00:00] with format [strict_date_optional_time||epoch_millis]: [failed to parse date field [2016-11-15 00:00:00] with format [strict_date_optional_time||epoch_millis]]]; nested: IllegalArgumentException[failed to parse date field [2016-11-15 00:00:00] with format [strict_date_optional_time||epoch_millis]]; nested: NotSerializableExceptionWrapper[date_time_parse_exception: Failed to parse with all enclosed parsers];\nShard[4]: OpenSearchParseException[failed to parse date field [2016-11-15 00:00:00] with format [strict_date_optional_time||epoch_millis]: [failed to parse date field [2016-11-15 00:00:00] with format [strict_date_optional_time||epoch_millis]]]; nested: IllegalArgumentException[failed to parse date field [2016-11-15 00:00:00] with format [strict_date_optional_time||epoch_millis]]; nested: NotSerializableExceptionWrapper[date_time_parse_exception: Failed to parse with all enclosed parsers];\n\nFor more details, please send request for Json format to see the raw response from OpenSearch engine.",
    "type": "SearchPhaseExecutionException"
  },
  "status": 400
}

Root cause - Currently for response JDBC format, when term/range query has timestamp field in the sql query, by default we are converting timestamps to epoch time in milliseconds when building query for Opensearch DSL which is causing the above exceptions. Instead for timestamp query string in epoch, we should send parsed formatted timestamp string as it is as part of the query similar to Date format.

Fixing this issue as part of this PR https://github.com/opensearch-project/sql/pull/2765