[BUG] DESCRIBE query returns incorrect data for text fields

[Yury-Fridlyand]

Describe the bug
DESCRIBE query returns incorrect data for fields of keyword type

To Reproduce
Steps to reproduce the behavior:

1. Create test data using sample given:

curl -s -H 'Content-Type: application/x-ndjson' -XPUT 'https://localhost:9200/dbg?pretty' -u admin:admin --insecure --data-binary @dbg_mappings.json

2. Execute SHOW TABLES LIKE dbg; and DESCRIBE TABLES LIKE dbg;:

curl -XPOST http://localhost:9200/_plugins/_sql -H 'Content-Type: application/json' -d '{"query": "show tables like dbg"}' -q
curl -XPOST http://localhost:9200/_plugins/_sql -H 'Content-Type: application/json' -d '{"query": "describe tables like dbg"}' -q

Actual behavior
The schema description and the output of describe query report that the data type is keyword. keyword is not a SQL data type.

Expected behavior
The data type should be [n]text or [n][var]char

Plugins
The default set:

curl "localhost:9200/_cat/plugins?h=component&pretty=true"
mapper-murmur3
opensearch-alerting
opensearch-anomaly-detection
opensearch-asynchronous-search
opensearch-index-management
opensearch-job-scheduler
opensearch-knn
opensearch-notebooks
opensearch-performance-analyzer
opensearch-reports-scheduler
opensearch-security
opensearch-sql
Host/Environment (please complete the following information):
Docker container v.1.0.0

[Yury-Fridlyand]

UPD
date column returned as timestamp in a query.
date0 mapping:

"date0" : { "type" : "date" }

A simple query:

curl -XPOST http://localhost:9200/_plugins/_sql -H 'Content-Type: application/json' -d '{"query": "SELECT `calcs`.`date0`, DATE(\"1972-04-07\") from calcs limit 5"}'
{
  "schema": [
    {
      "name": "date0",
      "type": "timestamp"
    },
    {
      "name": "DATE(\"1972-04-07\")",
      "type": "date"
    }
  ],
...

[chloe-zh]

1. For the keyword type, i think the response from the plugin is trying to return the original OpenSearch data type to users, and there is a type converter in the JDBC driver to convert the keywords etc. to SQL type like text / varchar
2. As for the date type, in the OpenSearch engine, all of the date and time types are defined as date type no matter if it is containing time or timezone information, so when the data comes in, the sql plugin maps it to timestamp in case of losing these information. We did not think up a good solution for this situation yet, do you have any proposal for it?

[Yury-Fridlyand]

See also #1296

[Yury-Fridlyand]

Further support for text will be added in #1038 and following tickets (1111, 1112, etc). As it was discussed, SQL plugin will continue reporting text as text and strings and keyword as keyword. JDBC driver should properly translate that into corresponding JDBC types.
Support for date type and actual type detection added in #1664 and #1821.