[FEATURE] Add new data type for text

Is your feature request related to a problem?

SQL plugin doesn't distinguish between text and keyword data types. OpenSearch supports aggregation on keywords and texts with fielddata and/or fields.

It is possible to aggregate on keyword or text (conditions apply)

opensearchsql> select sum(int0) from calcs GROUP BY str0;
fetched rows / total rows = 3/3
+-------------+
| sum(int0)   |
|-------------|
| 1           |
| 18          |
| 49          |
+-------------+

But impossible to aggregate on general text:

opensearchsql> select gender, count(firstname) from bank-with-null-values group by gender;
TransportError(500, 'SearchPhaseExecutionException', {'error': {'type': 'SearchPhaseExecutionException', 'reason': 'Error occurred in OpenSearch engine: all shards failed', 'details': 'Shard[0]: java.lang.IllegalArgumentException: Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [gender] in order to load field data by uninverting the inverted index. Note that this can use significant memory.\n\nFor more details, please send request for Json format to see the raw response from OpenSearch engine.'}, 'status': 503})

Existing mapping

See OpenSearch mapping samples available for aggregation:
https://github.com/opensearch-project/sql/blob/b56edc7b693594b87fe7b0ffd635eff79148e179/integ-test/src/test/resources/correctness/opensearch_dashboards_sample_data_flights.json#L25-L27
https://github.com/opensearch-project/sql/blob/b56edc7b693594b87fe7b0ffd635eff79148e179/integ-test/src/test/resources/correctness/opensearch_dashboards_sample_data_flights.json#L61-L69
https://github.com/opensearch-project/sql/blob/b56edc7b693594b87fe7b0ffd635eff79148e179/integ-test/src/test/resources/indexDefinitions/account_index_mapping.json#L12-L21
Not available for aggregation:
https://github.com/opensearch-project/sql/blob/b56edc7b693594b87fe7b0ffd635eff79148e179/integ-test/src/test/resources/indexDefinitions/bank_with_null_values_index_mapping.json#L16-L18

What solution would you like?

Have 2 different data types which are mapped to different JDBC/ODBC types.

What alternatives have you considered?

N/A

Do you have any additional context?

Opened on behalf of @kylepbit
Ref:

1. https://github.com/opensearch-project/sql/blob/b56edc7b693594b87fe7b0ffd635eff79148e179/sql-jdbc/src/main/java/org/opensearch/jdbc/types/OpenSearchType.java#L59-L61
2. https://github.com/opensearch-project/sql/blob/b56edc7b693594b87fe7b0ffd635eff79148e179/opensearch/src/main/java/org/opensearch/sql/opensearch/data/type/OpenSearchDataType.java#L25-L46
3. https://github.com/opensearch-project/sql/blob/b56edc7b693594b87fe7b0ffd635eff79148e179/core/src/main/java/org/opensearch/sql/data/type/ExprCoreType.java#L44-L47

Start with the referenced type definitions in sql-jdbc/src/main/java/org/opensearch/jdbc/types/OpenSearchType.java, opensearch/src/main/java/org/opensearch/sql/opensearch/data/type/OpenSearchDataType.java, and core/src/main/java/org/opensearch/sql/data/type/ExprCoreType.java. Compare these mappings with the linked index-definition samples and verify that keyword or fielddata-enabled text remains aggregatable while plain text maps to TEXT and is not treated the same way.