[BUG] DefaultSparkSqlFunctionResponseHandle throws SemanticCheckException on date type

[noCharger]

What is the bug?

DefaultSparkSqlFunctionResponseHandle throws SemanticCheckException on this doc

{
          "result": [
            "{'2010-02-11':'2010-02-11'}"
          ],
          "schema": [
            "{'column_name':'2010-02-11','data_type':'date'}"
          ],
          "jobRunId": "00fnu2h4tdjnm80b",
          "applicationId": "00fnodsi739nsg09",
          "dataSourceName": "myglue_test",
          "status": "SUCCESS",
          "error": "",
          "queryId": "VHppcjZmcU5CRG15Z2x1ZV90ZXN0",
          "queryText": "select date('2010-02-11')",
          "sessionId": "TG10RTNoNFJWeG15Z2x1ZV90ZXN0",
          "jobType": "interactive",
          "updateTime": 1731524603539,
          "queryRunTime": 685
        }
}        

"{\n "status": 500,\n "error": {\n "type": "SemanticCheckException",\n "reason": "There was internal problem at backend",\n "details": "timestamp:2010-02-11 in unsupported format, please use \u0027yyyy-MM-dd HH:mm:ss[.SSSSSSSSS]\u0027"\n }\n}"

How can one reproduce the bug?

Use async query api with query

select date('2010-02-11')

What is the expected behavior?

200, understand the result and schema

Do you have any additional context?
Stacktrace

[2024-11-13T20:03:14,930][ERROR][o.o.s.s.r.RestAsyncQueryManagementAction] [51dfc4a70ba6543b484007138c2fd46a] Error happened during request handling
org.opensearch.sql.exception.SemanticCheckException: timestamp:2010-02-11 in unsupported format, please use 'yyyy-MM-dd HH:mm:ss[.SSSSSSSSS]'
        at org.opensearch.sql.data.model.ExprTimestampValue.<init>(ExprTimestampValue.java:39)
        at org.opensearch.sql.spark.functions.response.DefaultSparkSqlFunctionResponseHandle.extractRow(DefaultSparkSqlFunctionResponseHandle.java:93)
        at org.opensearch.sql.spark.functions.response.DefaultSparkSqlFunctionResponseHandle.constructIteratorAndSchema(DefaultSparkSqlFunctionResponseHandle.java:58)
        at org.opensearch.sql.spark.functions.response.DefaultSparkSqlFunctionResponseHandle.<init>(DefaultSparkSqlFunctionResponseHandle.java:47)
        at org.opensearch.sql.spark.asyncquery.AsyncQueryExecutorServiceImpl.getAsyncQueryResults(AsyncQueryExecutorServiceImpl.java:77)
        at org.opensearch.sql.spark.transport.TransportGetAsyncQueryResultAction.doExecute(TransportGetAsyncQueryResultAction.java:55)
        at org.opensearch.sql.spark.transport.TransportGetAsyncQueryResultAction.doExecute(TransportGetAsyncQueryResultAction.java:28)
        at org.opensearch.action.support.TransportAction$RequestFilterChain.proceed(TransportAction.java:218)
        at org.opensearch.indexmanagement.controlcenter.notification.filter.IndexOperationActionFilter.apply(IndexOperationActionFilter.kt:39)
        at org.opensearch.action.support.TransportAction$RequestFilterChain.proceed(TransportAction.java:216)
        at org.opensearch.indexmanagement.rollup.actionfilter.FieldCapsFilter.apply(FieldCapsFilter.kt:118)
        at org.opensearch.action.support.TransportAction$RequestFilterChain.proceed(TransportAction.java:216)
        at org.opensearch.security.filter.SecurityFilter.apply0(SecurityFilter.java:395)
        at org.opensearch.security.filter.SecurityFilter.apply(SecurityFilter.java:165)
        at org.opensearch.action.support.TransportAction$RequestFilterChain.proceed(TransportAction.java:216)
        at org.opensearch.performanceanalyzer.action.PerformanceAnalyzerActionFilter.apply(PerformanceAnalyzerActionFilter.java:78)
        at org.opensearch.action.support.TransportAction$RequestFilterChain.proceed(TransportAction.java:216)
        at org.opensearch.action.support.TransportAction.execute(TransportAction.java:188)
        at org.opensearch.action.support.TransportAction.execute(TransportAction.java:107)
        at org.opensearch.client.node.NodeClient.executeLocally(NodeClient.java:110)
        at org.opensearch.client.node.NodeClient.doExecute(NodeClient.java:97)
        at org.opensearch.client.support.AbstractClient.execute(AbstractClient.java:476)
        at org.opensearch.sql.spark.rest.RestAsyncQueryManagementAction.lambda$executeGetAsyncQueryResultRequest$3(RestAsyncQueryManagementAction.java:165)
        at org.opensearch.sql.datasources.utils.Scheduler.lambda$withCurrentContext$0(Scheduler.java:30)
        at org.opensearch.common.util.concurrent.ThreadContext$ContextPreservingRunnable.run(ThreadContext.java:863)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
        at java.base/java.lang.Thread.run(Thread.java:840)
[2024-11-13T20:03:14,930][INFO ][c.a.c.e.logger           ] [51dfc4a70ba6543b484007138c2fd46a] GET /_plugins/_async_query/VHppcjZmcU5CRG15Z2x1ZV90ZXN0 pretty=true 500 INTERNAL_SERVER_ERROR 246 72

opensearch-project/opensearch-spark#875

[dblock]

[Catch All Triage - 1, 2, 3]

[dai-chen]

@noCharger It looks like the same issue in #3122. FYI, I was wondering if we can simply convert Spark response to our response format to solve all these problems.