[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: https://github.com/opensearch-project/sql/blob/a4f80663ecdd8cc7403d42cd7fdce06b0ccbd1fd/integ-test/src/test/resources/indexDefinitions/bank_index_mapping.json#L31-L32 https://github.com/opensearch-project/sql/blob/a4f80663ecdd8cc7403d42cd7fdce06b0ccbd1fd/integ-test/src/test/resources/indexDefinitions/bank_index_mapping.json#L19-L20 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 @ 662a9383e

Do you have any additional context?

Related to #1038 and to https://github.com/opensearch-project/observability/issues/1392

[Yury-Fridlyand]

Some technical details. On response serialization

• PPL calls to typeName function of ExprType which returns

  • STRING for ExprCoreType.STRING: https://github.com/opensearch-project/sql/blob/a4f80663ecdd8cc7403d42cd7fdce06b0ccbd1fd/core/src/main/java/org/opensearch/sql/data/type/ExprCoreType.java#L110-L111 (it is lowercased then)
  • string for OpenSearchDataType OPENSEARCH_TEXT and OPENSEARCH_TEXT_KEYWORD: https://github.com/opensearch-project/sql/blob/a4f80663ecdd8cc7403d42cd7fdce06b0ccbd1fd/opensearch/src/main/java/org/opensearch/sql/opensearch/data/type/OpenSearchDataType.java#L136-L137 where jdbcType field set to "string" in the costructor: https://github.com/opensearch-project/sql/blob/a4f80663ecdd8cc7403d42cd7fdce06b0ccbd1fd/opensearch/src/main/java/org/opensearch/sql/opensearch/data/type/OpenSearchDataType.java#L33

• SQL calls to legacyTypeName function of ExprType which returns

  • keyword for ExprCoreType.STRING: https://github.com/opensearch-project/sql/blob/a4f80663ecdd8cc7403d42cd7fdce06b0ccbd1fd/core/src/main/java/org/opensearch/sql/data/type/ExprCoreType.java#L115-L116 https://github.com/opensearch-project/sql/blob/a4f80663ecdd8cc7403d42cd7fdce06b0ccbd1fd/core/src/main/java/org/opensearch/sql/data/type/ExprCoreType.java#L85
  • text for OpenSearchDataType OPENSEARCH_TEXT and OPENSEARCH_TEXT_KEYWORD: https://github.com/opensearch-project/sql/blob/a4f80663ecdd8cc7403d42cd7fdce06b0ccbd1fd/opensearch/src/main/java/org/opensearch/sql/opensearch/data/type/OpenSearchDataType.java#L141-L142 https://github.com/opensearch-project/sql/blob/a4f80663ecdd8cc7403d42cd7fdce06b0ccbd1fd/opensearch/src/main/java/org/opensearch/sql/opensearch/data/type/OpenSearchDataType.java#L96-L97

[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