[BUG] Projecting a nested subfield returns only the first child

[dai-chen]

Query Information

PPL Command/Query:

source=nested_repro | fields id, events.name, events.status

Expected Result:
6 rows — one per events child (doc1:1, doc2:2, doc3:1, doc4:2).

Actual Result:
4 rows — one per parent document, containing only the first child:

['doc1', 'db_query',  'ok']
['doc2', 'db_query',  'error']     <- child {http_call, ok} dropped
['doc3', 'http_call', 'error']
['doc4', 'http_call', 'error']     <- child {db_query, ok} dropped

The same engine is internally inconsistent — stats sees all children while fields does not:

source=nested_repro | stats count() by events.name

returns db_query: 3, http_call: 3 (correct, matches a nested terms agg), yet only 2 projected rows ever contain db_query.

Dataset Information

Dataset/Schema Type

• OpenTelemetry (OTEL)
• Simple Schema for Observability (SS4O)
• Open Cybersecurity Schema Framework (OCSF)
• Custom (details below)

Index Mapping

{
  "mappings": {
    "properties": {
      "id": {"type": "keyword"},
      "events": {
        "type": "nested",
        "properties": {
          "name": {"type": "keyword"},
          "status": {"type": "keyword"}
        }
      }
    }
  }
}

Sample Data

{"id":"doc1","events":[{"name":"db_query","status":"ok"}]}
{"id":"doc2","events":[{"name":"db_query","status":"error"},{"name":"http_call","status":"ok"}]}
{"id":"doc3","events":[{"name":"http_call","status":"error"}]}
{"id":"doc4","events":[{"name":"http_call","status":"error"},{"name":"db_query","status":"ok"}]}

Bug Description

Issue Summary:
When a nested subfield is projected, only element [0] of the array is read. Remaining children are dropped with no warning, error, or row multiplication. Because the value is typed as a flat scalar rather than a collection, there is no way for a user to reach the other children.

Note: this is not the documented behavior of plugins.query.field_type_tolerance. That setting defaults to true ("preserve arrays") and does work on field roots — flipping it changes the output of fields events — but dotted subfield paths like events.name collapse to the first element in both states, so the setting is never consulted here.

Steps to Reproduce:

1. Create the index and load the 4 documents above.
2. PUT _cluster/settings {"transient":{"plugins.calcite.enabled":true}}
3. Run the query — 4 rows, first child only.
4. Compare to _source, which holds 6 children total.

Root cause:
core/src/main/java/org/opensearch/sql/data/model/ExprValueUtils.java:230, unchanged since #3476:

public static ExprValue resolveRefPaths(ExprValue value, List<String> paths) {
  ExprValue wholePathValue = value.keyValue(String.join(PATH_SEP, paths));
  // For array types only first index currently supported.
  if (value.type().equals(ExprCoreType.ARRAY)) {
    wholePathValue = value.collectionValue().getFirst().keyValue(paths.getFirst());
  }

This is reached from OpenSearchIndexEnumerator.resolveForCalcite. #3476 deliberately chose to flatten nested fields into scalar columns ("option1") over a PartiQL-like scoped approach (#3459), and the first-child shortcut is a direct consequence.

Impact:
Silently incomplete results whenever a nested subfield is projected — data loss with no signal to the user, and results that contradict stats on the same field in the same engine. Legacy SQL returns all children via nested(message.info); PPL Calcite has no equivalent.

Environment Information

OpenSearch Version: 3.9.0-SNAPSHOT (./gradlew run on main @ 96399c590)

Additional Details:

• Calcite (v3) engine, plugins.calcite.enabled=true
• Related: [BUG][Umbrella] Array values are not treated as atomic values in SQL/PPL operations #5333 (array-atomicity umbrella) covers the same shortcut for plain arrays; this issue is the nested-type manifestation
• Also causes the false negative in the companion nested-where correlation bug when pushdown is disabled
• Tracked under [META] Support nested field type in Calcite #4625