SQL: grouping by double casted date value throws exception

[astefan]

Test data:

{"index":{}}
{"date2":"1977-04-20","date0":"2004-04-15","num4":10.85,"int0":1}

Query and thrown exception:

sql> SELECT CONVERT(CONVERT(date0, SQL_VARCHAR), SQL_DATE) FROM calcs GROUP BY 1;
Server error [Server encountered an error [Invalid date key returned: 2004-04-15T00:00:00.000Z]. [SqlIllegalArgumentException[Invalid date key returned: 2004-04-15T00:00:00.000Z]
        at org.elasticsearch.xpack.sql.execution.search.extractor.CompositeKeyExtractor.extract(CompositeKeyExtractor.java:100)
        at org.elasticsearch.xpack.sql.expression.gen.processor.BucketExtractorProcessor.process(BucketExtractorProcessor.java:50)
        at org.elasticsearch.xpack.sql.execution.search.extractor.ComputingExtractor.extract(ComputingExtractor.java:73)
        at org.elasticsearch.xpack.sql.execution.search.CompositeAggsRowSet.getColumn(CompositeAggsRowSet.java:64)
        at org.elasticsearch.xpack.sql.session.AbstractRowSet.column(AbstractRowSet.java:18)
        at org.elasticsearch.xpack.sql.session.RowView.forEachColumn(RowView.java:38)
        at org.elasticsearch.xpack.sql.plugin.TransportSqlQueryAction.lambda$createResponse$4(TransportSqlQueryAction.java:117)
        at org.elasticsearch.xpack.sql.session.RowSet.forEachRow(RowSet.java:32)
        at org.elasticsearch.xpack.sql.plugin.TransportSqlQueryAction.createResponse(TransportSqlQueryAction.java:115)
        at org.elasticsearch.xpack.sql.plugin.TransportSqlQueryAction.createResponse(TransportSqlQueryAction.java:110)
        at org.elasticsearch.xpack.sql.plugin.TransportSqlQueryAction.lambda$operation$0(TransportSqlQueryAction.java:83)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:60)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:60)
        at org.elasticsearch.xpack.sql.execution.search.Querier$CompositeActionListener.handleResponse(Querier.java:227)
        at org.elasticsearch.xpack.sql.execution.search.Querier$BaseActionListener.onResponse(Querier.java:408)
        at org.elasticsearch.xpack.sql.execution.search.Querier$BaseActionListener.onResponse(Querier.java:384)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:60)
        at org.elasticsearch.action.search.AbstractSearchAsyncAction.onResponse(AbstractSearchAsyncAction.java:313)
        at org.elasticsearch.action.search.AbstractSearchAsyncAction.onResponse(AbstractSearchAsyncAction.java:50)
        at org.elasticsearch.action.search.FetchSearchPhase$3.run(FetchSearchPhase.java:213)
        at org.elasticsearch.action.search.AbstractSearchAsyncAction.executePhase(AbstractSearchAsyncAction.java:160)
        at org.elasticsearch.action.search.AbstractSearchAsyncAction.executeNextPhase(AbstractSearchAsyncAction.java:153)
        at org.elasticsearch.action.search.ExpandSearchPhase.run(ExpandSearchPhase.java:120)
        at org.elasticsearch.action.search.AbstractSearchAsyncAction.executePhase(AbstractSearchAsyncAction.java:160)
        at org.elasticsearch.action.search.AbstractSearchAsyncAction.executeNextPhase(AbstractSearchAsyncAction.java:153)
        at org.elasticsearch.action.search.FetchSearchPhase.moveToNextPhase(FetchSearchPhase.java:206)
        at org.elasticsearch.action.search.FetchSearchPhase.lambda$innerRun$2(FetchSearchPhase.java:104)
        at org.elasticsearch.action.search.FetchSearchPhase.innerRun(FetchSearchPhase.java:110)
        at org.elasticsearch.action.search.FetchSearchPhase.access$000(FetchSearchPhase.java:44)
        at org.elasticsearch.action.search.FetchSearchPhase$1.doRun(FetchSearchPhase.java:86)
        at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:759)
        at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37)
        at org.elasticsearch.common.util.concurrent.TimedRunnable.doRun(TimedRunnable.java:41)
        at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:834)
]]

[elasticmachine]

Pinging @elastic/es-search

[imotov]

The original repro no longer reproduces the issue because it was partially fixed by #37693. However, I stumbled upon another issue that still triggers the same error in the same place. I was able to reproduce it with this:

SELECT CONVERT(CONVERT(date0, SQL_VARCHAR), SQL_TIMESTAMP) FROM calcs GROUP BY 1;