[BUG] SQL query doesn't honor date format in OpenSearch index mapping #794
Description
What is the bug?
It seems SQL query engine doesn't honor what's configured in OpenSearch index mapping for date field. This causes problems in different queries with datetime field involved. See examples below.
How can one reproduce the bug?
As documented, "strict_date_optional_time||epoch_millis" is the default format if not specified in index mapping. The issue happens when custom date format is configured as below. Note that this is mostly due to the gaps between engine v2 and the legacy (which may not have these issues at all).
Issue 1: Datetime literal parsing problem
With epoch_millis format removed in mapping, the previous work query throws exception now. From the error message, it seems caused by epoch timestamp used in DSL translated rather than the only strict_date_optional_time configured. Note that OpenSearch doesn't complain this in any syntax/semantic check (probably due to missing semantic check), but throw exception at execution time instead.
PUT my-index-000002
{
"mappings": {
"properties": {
"date": {
"type": "date",
"format": "strict_date_optional_time"
}
}
}
}
PUT my-index-000002/_doc/3
{ "date": "2015-01-01T12:10:30Z" }
POST _plugins/_sql
{
"query": "SELECT * FROM my-index-000002 WHERE `date` < '2022-08-20 23:59:59.999' "
}
{
"error": {
"type": "SearchPhaseExecutionException",
"reason": "Error occurred in OpenSearch engine: all shards failed",
"details": "Shard[0]: OpenSearchParseException[failed to parse date field [1661039999999] with format [strict_date_optional_time]: [failed to parse date field [1661039999999] with format [strict_date_optional_time]]]; nested: IllegalArgumentException[failed to parse date field [1661039999999] with format [strict_date_optional_time]]; nested: NotSerializableExceptionWrapper[date_time_parse_exception: Text '1661039999999' could not be parsed at index 0];\n\nFor more details, please send request for Json format to see the raw response from OpenSearch engine."
},
"status": 503
}
Issue 2: Datetime value parsing problem
Related: #126, opendistro-for-elasticsearch/sql#1062
No matter what date format configured, OpenSearchExprValueFactory always uses the hardcoding formatter in
What is the expected behavior?
OpenSearch SQL/PPL should honor the date format in index mapping and parse date value from OpenSearch or date literals in query accordingly.
Currently only data type is returned and associated with field. One approach to improve this is reading datetime format from OpenSearch along with basic field type info. Code:
What is your host/environment?
- OpenSearch 2.2
- Plugins: SQL
Do you have any screenshots?
N/A
Do you have any additional context?
Similar issues may apply to PPL as well due to single core engine shared across languages.