[BUG] fields starting with `__` is missing in the returned result when query by SQL with extra `filter`

[yxwyoyoyo]

What is the bug? Fields starting with __ is missing in the returned result when query by SQL with extra filter.

How can one reproduce the bug? Steps to reproduce the behavior:

1. Create an index with serval fields, some of them are starting with __. For example, an index test, contains a field whose name is '__time'.
2. Construct some document.
3. Query with the SQL select * from test and extra filter.

What is the expected behavior? The result should contains __time.

What is your host/environment?

• environment: docker.
• version: 1.0.1、2.2.0
• plugin: opensearch-sql

Do you have any screenshots?

Do you have any additional context? It seems that the query is fallback to legacy sql engine. In this situation, when populating columns, it will ignore meta fields (starting with _). https://github.com/opensearch-project/sql/blob/eeb90cf44d6031204e8bbab586f0c6dbf87ae81b/legacy/src/main/java/org/opensearch/sql/legacy/executor/format/SelectResultSet.java#L434-L480 https://github.com/opensearch-project/sql/blob/eeb90cf44d6031204e8bbab586f0c6dbf87ae81b/legacy/src/main/java/org/opensearch/sql/legacy/esdomain/mapping/FieldMapping.java#L95-L97

[dai-chen]

@yxwyoyoyo Thanks for reporting the issue! Your investigation is very helpful. In our engine v2, we did miss this support for identifier starting with underscore: https://github.com/opensearch-project/sql/blob/main/sql/src/main/antlr/OpenSearchSQLLexer.g4#L428

Could you elaborate a little your use case and if this kind of naming is required? Thanks!

[yxwyoyoyo]

@dai-chen In our use case, we provide service based on ES and open the ability to customize mapping. In addition, we have serval system fields to save meta data, so we preserve names start with __ as system fields. We also took other prefixes into consideration, but none of them were ideal.

[dai-chen]

@dai-chen In our use case, we provide service based on ES and open the ability to customize mapping. In addition, we have serval system fields to save meta data, so we preserve names start with __ as system fields. We also took other prefixes into consideration, but none of them were ideal.

Thanks for the info! Will evaluate if we can support this.

[acarbonetto]

@dai-chen this issue can be closed as part of https://github.com/opensearch-project/sql/pull/1456 We now accept any field with __ or _ so long as the field is not reserved by OpenSearch.