[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 _).

sql/legacy/src/main/java/org/opensearch/sql/legacy/executor/format/SelectResultSet.java

Lines 434 to 480 in eeb90cf

	/*
	* Unnecessary fields (ex. _index, _parent) are ignored.
	* Fields like field.keyword will be ignored when isSelectAll is true but will be returned if
	* explicitly selected.
	*/
	FieldMapping field = new FieldMapping(fieldName, typeMappings, fieldMap);
	if (!field.isMetaField()) {
	if (field.isMultiField() && !field.isSpecified()) {
	continue;
	}
	if (field.isPropertyField() && !field.isSpecified() && !field.isWildcardSpecified()) {
	continue;
	}
	/*
	* Three cases regarding Type:
	* 1. If Type exists, create Column
	* 2. If Type doesn't exist and isSelectAll() is false, throw exception
	* 3. If Type doesn't exist and isSelectAll() is true, Column creation for fieldName is skipped
	*/
	String type = field.type().toUpperCase();
	if (Schema.hasType(type)) {
	// If the current field is a group key, we should use alias as the identifier
	boolean isGroupKey = false;
	Select select = (Select) query;
	if (null != select.getGroupBys()
	&& !select.getGroupBys().isEmpty()
	&& select.getGroupBys().get(0).contains(fieldMap.get(fieldName))) {
	isGroupKey = true;
	}
	columns.add(
	new Schema.Column(
	fieldName,
	fetchAlias(fieldName, fieldMap),
	Schema.Type.valueOf(type),
	isGroupKey
	)
	);
	} else if (!isSelectAll()) {
	throw new IllegalArgumentException(
	String.format("%s fieldName types are currently not supported.", type));
	}
	}
	}

sql/legacy/src/main/java/org/opensearch/sql/legacy/esdomain/mapping/FieldMapping.java

Lines 95 to 97 in eeb90cf

	public boolean isMetaField() {
	return fieldName.startsWith("_");
	}

[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 #1456
We now accept any field with __ or _ so long as the field is not reserved by OpenSearch.