[BUG] Nested Date Field Can't Filter In WHERE Clause Predicate Expression

[forestmvey]

What is the bug?
The nested function in SQL fails when used with a date type in the WHERE clause.

Dataset

{"index":{"_id":"1"}}
{"title":"Investment secrets","body":"What they don't tell you ...","tags":["shares","equities"],"comments":[{"name":"Mary Brown","comment":"Lies, lies, lies","age":42,"stars":1,"date":"2014-10-18"},{"name":"John Smith","comment":"You're making it up!","age":28,"stars":2,"date":"2014-10-16"}]}

Mapping*

{
  "mappings": {
    "properties": {
      "title": {
        "type": "keyword",
        "index": "true"
      },
      "body": {
        "type": "keyword",
        "index": "true"
      },
      "tags": {
        "type": "keyword",
        "index": "true"
      },
      "comments": {
        "type": "nested",
        "properties": {
          "name": {
            "type": "keyword",
            "index": "true"
          },
          "comment": {
            "type": "keyword",
            "index": "true"
          },
          "age": {
            "type": "long"
          },
          "stars": {
            "type": "long"
          },
          "date": {
            "type": "date",
            "format": "yyyy-MM-dd"
          }
        }
      }
    }
  }
}

SELECT nested(comments.date) FROM nested WHERE nested(comments.date) > '2014-09-12';

OR

SELECT nested(comments.date) FROM nested WHERE nested(comments.date) > DATE('2014-09-12');

Result:

{
  "error": {
    "reason": "Invalid SQL query",
    "details": "Illegal argument on right side of predicate expression: cast_to_timestamp(\"2014-09-12\")",
    "type": "IllegalArgumentException"
  },
  "status": 400
}

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

1. Load dataset into opensearch.
2. Execute Query.
3. Validate Results.

What is the expected behavior?
Query should succeed and return filtered results.

What is your host/environment?

• OS: IOS
• Version 13.4.1
• Plugins SQL

Do you have any screenshots?
N/A

Do you have any additional context?
Found a workaround using alternate syntax for nested function:

SELECT nested(comments.date) FROM nested WHERE nested(comments, comments.date > '2014-09-12');

[acarbonetto]

Related: #856