[BUG] SQL and PPL responses different types for the same things

[Yury-Fridlyand]

What is the bug?

PPL returns string data type for all string columns.
SQL returns text for text data and keyword for all rest.

text and keyword are not the same thing and a user should be able to distinguish them.

How can one reproduce the bug?

Use sample data from integration tests, for example bank index.
Check mapping for gender and for city:

sql/integ-test/src/test/resources/indexDefinitions/bank_index_mapping.json

Lines 31 to 32 in a4f8066

	"gender": {
	"type": "text",

sql/integ-test/src/test/resources/indexDefinitions/bank_index_mapping.json

Lines 19 to 20 in a4f8066

	"city": {
	"type": "keyword"

And then compare types reported by
PPL

$ curl -s -XPOST http://localhost:9200/_plugins/_ppl -H 'Content-Type: application/json' -d '{"query": "source=bank | fields gender, city"}' | grep '"type"'
      "type": "string"
      "type": "string"

SQL

$ curl -s -XPOST http://localhost:9200/_plugins/_sql -H 'Content-Type: application/json' -d '{"query": "select gender, city from bank"}' | grep '"type"'
      "type": "text"
      "type": "keyword"

What is the expected behavior?

Returned type should be same for PPL and SQL. It should match index mapping for all columns given in index.

What is your host/environment?

2.x @ 662a938

Do you have any additional context?

Related to #1038 and to opensearch-project/observability#1392

[Yury-Fridlyand]

Some technical details. On response serialization

• PPL calls to typeName function of ExprType which returns

  • STRING for ExprCoreType.STRING:

    sql/core/src/main/java/org/opensearch/sql/data/type/ExprCoreType.java

    Lines 110 to 111 in a4f8066

    	public String typeName() {
    	return this.name();

    (it is lowercased then)
  • string for OpenSearchDataType OPENSEARCH_TEXT and OPENSEARCH_TEXT_KEYWORD:

    sql/opensearch/src/main/java/org/opensearch/sql/opensearch/data/type/OpenSearchDataType.java

    Lines 136 to 137 in a4f8066

    	public String typeName() {
    	return jdbcType;

    where jdbcType field set to "string" in the costructor:

    sql/opensearch/src/main/java/org/opensearch/sql/opensearch/data/type/OpenSearchDataType.java

    Line 33 in a4f8066

    OPENSEARCH_TEXT(Collections.singletonList(STRING), "string") {

• SQL calls to legacyTypeName function of ExprType which returns

  • keyword for ExprCoreType.STRING:

    sql/core/src/main/java/org/opensearch/sql/data/type/ExprCoreType.java

    Lines 115 to 116 in a4f8066

    	public String legacyTypeName() {
    	return LEGACY_TYPE_NAME_MAPPING.getOrDefault(this, this.name());

    sql/core/src/main/java/org/opensearch/sql/data/type/ExprCoreType.java

    Line 85 in a4f8066

    .put(STRING, "keyword")

  • text for OpenSearchDataType OPENSEARCH_TEXT and OPENSEARCH_TEXT_KEYWORD:

    sql/opensearch/src/main/java/org/opensearch/sql/opensearch/data/type/OpenSearchDataType.java

    Lines 141 to 142 in a4f8066

    	public String legacyTypeName() {
    	return LEGACY_TYPE_NAME_MAPPING.getOrDefault(this, typeName());

    sql/opensearch/src/main/java/org/opensearch/sql/opensearch/data/type/OpenSearchDataType.java

    Lines 96 to 97 in a4f8066

    	.put(OPENSEARCH_TEXT, "text")
    	.put(OPENSEARCH_TEXT_KEYWORD, "text")

[acarbonetto]

Please also make sure https://github.com/opensearch-project/sql/blob/main/docs/user/general/datatypes.rst#data-types-mapping and https://github.com/opensearch-project/sql/blob/main/docs/user/ppl/general/datatypes.rst#data-types-mapping are consistent