[BUG] date comparisons fail when Calcite plugin is on #4969
Description
Query Information
PPL Command/Query:
POST /_plugins/_ppl
{
"query": "search source=test-index | where timestamp_epoch_millis >= '2023-12-31 12:00:00'"
}
POST /_plugins/_ppl
{
"query": "search source=test-index | where timestamp_datetime_string >= '2023-12-31 12:00:00'"
}
POST /_plugins/_ppl
{
"query": "search source=test-index | where timestamp_epoch_millis >= date_sub(str_to_date('2024-01-01 12:00:00', '%Y-%m-%d %H:%i:%S'), interval 24 hour)"
}
POST /_plugins/_ppl
{
"query": "search source=test-index | where timestamp_datetime_string >= date_sub(str_to_date('2024-01-01 12:00:00', '%Y-%m-%d %H:%i:%S'), interval 24 hour)"
}
Expected Result:
When index is instantiated as explained below, result should include 2 data rows
Actual Result:
When Calcite plugin is off, all queries work as expected. When Calcite plugin is on, in all cases I get SearchPhaseExecutionException errors like the following:
{
"error": {
"reason": "Error occurred in OpenSearch engine: all shards failed",
"details": "Shard[0]: OpenSearchParseException[failed to parse date field [2023-12-31T12:00:00.000Z] with format [epoch_millis]: [failed to parse date field [2023-12-31T12:00:00.000Z] with format [epoch_millis]]]; nested: IllegalArgumentException[failed to parse date field [2023-12-31T12:00:00.000Z] with format [epoch_millis]]; nested: NotSerializableExceptionWrapper[date_time_parse_exception: Failed to parse with all enclosed parsers];\nShard[1]: OpenSearchParseException[failed to parse date field [2023-12-31T12:00:00.000Z] with format [epoch_millis]: [failed to parse date field [2023-12-31T12:00:00.000Z] with format [epoch_millis]]]; nested: IllegalArgumentException[failed to parse date field [2023-12-31T12:00:00.000Z] with format [epoch_millis]]; nested: NotSerializableExceptionWrapper[date_time_parse_exception: Failed to parse with all enclosed parsers];\nShard[2]: OpenSearchParseException[failed to parse date field [2023-12-31T12:00:00.000Z] with format [epoch_millis]: [failed to parse date field [2023-12-31T12:00:00.000Z] with format [epoch_millis]]]; nested: IllegalArgumentException[failed to parse date field [2023-12-31T12:00:00.000Z] with format [epoch_millis]]; nested: NotSerializableExceptionWrapper[date_time_parse_exception: Failed to parse with all enclosed parsers];\nShard[3]: OpenSearchParseException[failed to parse date field [2023-12-31T12:00:00.000Z] with format [epoch_millis]: [failed to parse date field [2023-12-31T12:00:00.000Z] with format [epoch_millis]]]; nested: IllegalArgumentException[failed to parse date field [2023-12-31T12:00:00.000Z] with format [epoch_millis]]; nested: NotSerializableExceptionWrapper[date_time_parse_exception: Failed to parse with all enclosed parsers];\nShard[4]: OpenSearchParseException[failed to parse date field [2023-12-31T12:00:00.000Z] with format [epoch_millis]: [failed to parse date field [2023-12-31T12:00:00.000Z] with format [epoch_millis]]]; nested: IllegalArgumentException[failed to parse date field [2023-12-31T12:00:00.000Z] with format [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
}
Dataset Information
Dataset/Schema Type
- OpenTelemetry (OTEL)
- Simple Schema for Observability (SS4O)
- Open Cybersecurity Schema Framework (OCSF)
- Custom (details below)
Index Mapping
PUT /test-index
{
"mappings": {
"properties": {
"timestamp_epoch_millis": {
"type": "date",
"format": "epoch_millis"
},
"timestamp_datetime_string": {
"type": "date",
"format": "date_hour_minute_second"
},
"message": {
"type": "text"
}
}
}
}Sample Data
POST /test-index/_bulk
{"index":{"_id":"1"}}
{"timestamp_epoch_millis":1703980800000,"timestamp_datetime_string":"2023-12-31T00:00:00","message":"old document"}
{"index":{"_id":"2"}}
{"timestamp_epoch_millis":1704067200000,"timestamp_datetime_string":"2024-01-01T00:00:00","message":"recent document"}
{"index":{"_id":"3"}}
{"timestamp_epoch_millis":1704153600000,"timestamp_datetime_string":"2024-01-02T00:00:00","message":"newest document"}Bug Description
Issue Summary:
When Calcite plugin is on, the above query result in a SearchPhaseExecutionException
Steps to Reproduce:
- Set up the index with the above mapping and documents
- Turn on calcite plugin
PUT _plugins/_query/settings
{
"transient": {
"plugins.calcite.enabled": true
}
}
- Run PPL queries provided on top of the issue
Impact:
I don't know how else to do time filtering in these cases
Environment Information
OpenSearch Version:
Tested on 2.19, 3.1, 3.3
Additional Details:
AWS managed cluster
Looks similar to #4490