[BUG] Filters on nested fields return wrong results due to uncorrelated predicates

[dai-chen]

Query Information

PPL Command/Query:

source=nested_repro | where events.name = 'db_query' and events.status = 'ok' | stats count()

Expected Result:
2 — only doc1 and doc4 have a single events child satisfying both predicates, i.e. ∃e(A ∧ B).

Actual Result:
3 — doc2 also matches, because the two predicates are satisfied by different children ({db_query, error} and {http_call, ok}). The engine evaluates (∃e A) ∧ (∃e B).

With plugins.calcite.pushdown.enabled=false the same query returns 1, missing doc4. So both modes are wrong, in opposite directions:

Path	... fields id	vs expected
Raw DSL, correlated nested query	doc1, doc4	✅ correct
PPL, pushdown on (default)	doc1, doc2, doc4	false positive
PPL, pushdown off	doc1	false negative

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"}]}

doc2 is the decoy: both predicates are satisfied, but by different children. doc4 is the mirror: the matching child is not the first one.

Bug Description

Issue Summary:
A where clause referencing two subfields of the same nested path is pushed down as two sibling nested clauses under one bool.must. Each nested clause is scored independently, so the predicates need not be satisfied by the same child document. Nested identity is lost in the logical plan — events.name and events.status are modeled as ordinary flat scalar columns with no shared correlation scope.

_explain (pushdown on):

{"bool":{"must":[
  {"nested":{"query":{"term":{"events.name":{"value":"db_query"}}},"path":"events","score_mode":"none"}},
  {"nested":{"query":{"term":{"events.status":{"value":"ok"}}},"path":"events","score_mode":"none"}}
]}}

The correct DSL is a single nested clause wrapping the whole conjunction:

{"nested":{"path":"events","query":{"bool":{"must":[
  {"term":{"events.name":"db_query"}},
  {"term":{"events.status":"ok"}}
]}}}}

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 — get 3, expected 2.
4. Confirm ground truth with the correlated nested DSL above — returns doc1, doc4.

Root cause:
Each leaf predicate wraps itself in a nested query, at opensearch/src/main/java/org/opensearch/sql/opensearch/request/PredicateAnalyzer.java:1329:

if (rel != null && !Strings.isNullOrEmpty(rel.nestedPath)) {
  return nestedQuery(rel.nestedPath, builder, ScoreMode.None);
}

CompoundQueryExpression.and() (same file, ~line 1255) then must()s the already-wrapped children with no grouping by nestedPath. Introduced by #4825 (3.5.0), which added nested filter pushdown on top of the flattened-column model from #3476.

The pushdown-off path is wrong for a separate reason — it reads only the first array element (ExprValueUtils.resolveRefPaths, core/src/main/java/org/opensearch/sql/data/model/ExprValueUtils.java:230), so doc1 passing is accidental. Filed separately.

Note the existing expected-output tests pin the incorrect shape:
integ-test/src/test/resources/expectedOutput/calcite/filter_root_and_nested.yaml and filter_multiple_nested_cascaded_range.yaml. They will need updating.

Impact:
Silent wrong results for any where combining two subfields of the same nested path — a routine pattern in observability data (events, spans, attributes). No error is raised and no workaround exists: pushdown on over-counts, pushdown off under-counts. The legacy SQL engine handles this correctly via PartiQL-style scoping (nested(message, message.info = 'a' AND message.author = 'e'), see integ-test/src/test/java/org/opensearch/sql/sql/NestedIT.java:381); PPL Calcite has no equivalent construct.

Environment Information

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

Additional Details:

• Calcite (v3) engine, plugins.calcite.enabled=true
• Reproduces with pushdown both enabled and disabled (different incorrect answers)
• Tracked under [META] Support nested field type in Calcite #4625