SQL: multi-conditionals on date fields with NOW() don't use the format for range queries #48033
Description
SELECT hire_date FROM test_emp WHERE hire_date >= NOW() - INTERVAL 200 YEARS AND hire_date < NOW() - INTERVAL 2 YEARS
will end up returning an error:
"root_cause": [
{
"type": "parse_exception",
"reason": "failed to parse date field [1819-10-15T04:45:20.641Z] with format [yyyy-MM-dd' 'HH:mm:ss]: [failed to parse date field [1819-10-15T04:45:20.641Z] with format [yyyy-MM-dd' 'HH:mm:ss]]"
}
]
The error above does also happen only when the date format of the hire_date field is a custom one (non-default).
The reason for the error seems to be the lack of format attribute (added following this PR) on the range ES query generated for this sql query, which should enforce the range query to one known format - the one of NOW(), CURRENT_DATE() functions.