SQL: TIMESTAMP literal parsing failure when compared against DATETIME

[bpintea]

With given calcs index mapping:

       "time0" : {
          "type" : "date",
          "format" : "yyyy-MM-dd' 'HH:mm:ss"
        }

and source:

"time0" : "1899-12-30 21:07:32"

the query:
SELECT CAST(time0 AS DATETIME) AS a FROM calcs WHERE a IN ({ts '1899-12-30 21:07:32'})
will fail with:

    "root_cause": [
      {
        "type": "parse_exception",
        "reason": "failed to parse date field [1899-12-30T21:07:32Z] with format [yyyy-MM-dd' 'HH:mm:ss]: [failed to parse date field [1899-12-30T21:07:32Z] with format [yyyy-MM-dd' 'HH:mm:ss]]"
      }
    ]

and stacktrace:

[...]
Caused by: org.elasticsearch.ElasticsearchParseException: failed to parse date field [1899-12-30T21:07:32Z] with format [yyyy-MM-dd' 'HH:mm:ss]: [failed to parse date field [1899-12-30T21:07:32Z] with format [yyyy-MM-dd' 'HH:mm:ss]]
        at org.elasticsearch.common.time.JavaDateMathParser.parseDateTime(JavaDateMathParser.java:235) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.common.time.JavaDateMathParser.parse(JavaDateMathParser.java:76) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.index.mapper.DateFieldMapper$DateFieldType.parseToLong(DateFieldMapper.java:402) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.index.mapper.DateFieldMapper$DateFieldType.rangeQuery(DateFieldMapper.java:368) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.index.mapper.DateFieldMapper$DateFieldType.termQuery(DateFieldMapper.java:346) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.index.mapper.MappedFieldType.termsQuery(MappedFieldType.java:330) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.index.query.TermsQueryBuilder.doToQuery(TermsQueryBuilder.java:451) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.index.query.AbstractQueryBuilder.toQuery(AbstractQueryBuilder.java:99) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.index.query.QueryShardContext.lambda$toQuery$1(QueryShardContext.java:278) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.index.query.QueryShardContext.toQuery(QueryShardContext.java:290) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.index.query.QueryShardContext.toQuery(QueryShardContext.java:277) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.search.SearchService.parseSource(SearchService.java:738) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.search.SearchService.createContext(SearchService.java:586) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.search.SearchService.createAndPutContext(SearchService.java:545) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.search.SearchService.executeQueryPhase(SearchService.java:348) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.search.SearchService.lambda$executeQueryPhase$1(SearchService.java:340) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.action.ActionListener.lambda$map$2(ActionListener.java:145) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62) [elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.search.SearchService.lambda$rewriteShardRequest$7(SearchService.java:1043) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.action.ActionRunnable$1.doRun(ActionRunnable.java:45) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.common.util.concurrent.TimedRunnable.doRun(TimedRunnable.java:44) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:769) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) ~[elasticsearch-8.0.0-SNAPSHOT.jar:8.0.0-SNAPSHOT]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[?:?]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[?:?]
        at java.lang.Thread.run(Thread.java:835) ~[?:?]
[...]

[elasticmachine]

Pinging @elastic/es-search

[matriv]

master: 07c9770
7.x: 27265f0