[BUG] expand on a scalar, object, or flat_object field returns 500 or splits it into KEY and VALUE rows

Start in CalciteRelNodeVisitor.visitExpand near line 4453, where the field is analyzed and passed to buildExpandRelNode, and compare it with visitMvExpand near line 4680, which already checks the field type first. Reproduce with source=exps | expand n | fields host and the object and flat_object queries, then check how SyncErrorReportRenderer.isClientError maps the resulting exceptions. Done means scalar, object, flat_object and object-holding-array fields return HTTP 400 naming the field and its type, while nested expansion still returns one row per element.

Query Information

PPL Command/Query:

source=exps | expand n | fields host

n is a long field holding one value.

Expected Result: HTTP 400 naming the field and its type, for any field that is not nested. The expand documentation states Only nested arrays are supported.

Actual Result: HTTP 500.

{
  "error": {
    "context": {
      "stage": "analyzing",
      "stage_description": "Parsing and validating the query"
    },
    "reason": "UNNEST argument must be a collection",
    "details": "UNNEST argument must be a collection",
    "location": ["while preparing and validating the query plan"],
    "code": "UNKNOWN",
    "type": "CalciteException"
  },
  "status": 500
}

The query is rejected while analyzing, which is a user error, yet the status is 500 and the message is Calcite's internal wording.

f type	expand f
keyword	500
long	500
double	500
boolean	500
date	500
ip	500
geo_point	500
object	200, one row per key in KEY and VALUE columns
flat_object	200, one row per key in KEY and VALUE columns
object holding an array	500 at execution
nested	200, one row per element

Each row is a single index with f of that type. Every scalar 500 has the body above.

object and flat_object are not arrays, yet both return 200 with a result the documentation does not describe. source=exps | fields host, o | expand o turns one document into one row per key of o, drops o, and adds KEY and VALUE columns. The schema types VALUE as string, while the second row holds the number 5.

{
  "schema": [
    {
      "name": "host",
      "type": "string"
    },
    {
      "name": "KEY",
      "type": "string"
    },
    {
      "name": "VALUE",
      "type": "string"
    }
  ],
  "datarows": [
    [
      "a",
      "a",
      "x"
    ],
    [
      "a",
      "b",
      5
    ]
  ],
  "total": 2,
  "size": 2
}

An object field holding an array of objects passes analysis and fails at execution. source=exps | fields host, oa | expand oa returns HTTP 500.

{
  "error": {
    "context": {
      "stage": "executing",
      "stage_description": "Running the query"
    },
    "reason": "java.sql.SQLException: exception while executing query: class java.util.ArrayList cannot be cast to class java.util.Map (java.util.ArrayList and java.util.Map are in module java.base of loader 'bootstrap')",
    "details": "java.sql.SQLException: exception while executing query: class java.util.ArrayList cannot be cast to class java.util.Map (java.util.ArrayList and java.util.Map are in module java.base of loader 'bootstrap')",
    "location": ["while running the query"],
    "code": "UNKNOWN",
    "type": "RuntimeException"
  },
  "status": 500
}

Dataset Information

Dataset/Schema Type

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

Index Mapping

PUT exps { "mappings": { "properties": { "host": { "type": "keyword" }, "n": { "type": "long" }, "o": { "properties": { "a": { "type": "keyword" }, "b": { "type": "long" } } }, "oa": { "properties": { "a": { "type": "keyword" } } }, "fo": { "type": "flat_object" }, "nd": { "type": "nested", "properties": { "a": { "type": "keyword" } } } } } }

Sample Data

POST exps/_doc/1?refresh=true  { "host": "a", "n": 5, "o": { "a": "x", "b": 5 }, "oa": [ { "a": "x" }, { "a": "y" } ], "fo": { "a": "x" }, "nd": [ { "a": "x" }, { "a": "y" } ] }

Bug Description

Issue Summary

CalciteRelNodeVisitor.visitExpand at line 4453 analyzes the field and passes it straight to buildExpandRelNode at line 4462, with no type check. buildExpandRelNode ends in relBuilder.uncollect. Calcite's Uncollect.deriveUncollectRowType accepts an ARRAY, a MULTISET or a MAP, which it splits into KEY and VALUE columns, and throws CalciteException UNNEST argument must be a collection for anything else. PPL types a scalar field as a scalar SQL type, an object as (VARCHAR, ANY) MAP, a flat_object as (VARCHAR, VARCHAR) MAP and a nested field as an array. So a scalar reaches the throw, and an object or flat_object is unnested as a map. The type comes from the mapping, so an object holding an array is still a MAP at analysis, and the cast of its ArrayList value to Map fails at execution.

SyncErrorReportRenderer.isClientError lists neither CalciteException nor ClassCastException, so both failures return 500.

visitMvExpand at line 4680 already checks the type before it calls the same buildExpandRelNode at line 4710. It returns the input unchanged when the field is neither an array nor a multiset.

Steps to Reproduce

curl -s -XPUT "localhost:9200/exps" -H 'Content-Type: application/json' \
  -d '{"mappings":{"properties":{"host":{"type":"keyword"},"n":{"type":"long"},"o":{"properties":{"a":{"type":"keyword"},"b":{"type":"long"}}},"oa":{"properties":{"a":{"type":"keyword"}}},"fo":{"type":"flat_object"},"nd":{"type":"nested","properties":{"a":{"type":"keyword"}}}}}}'
curl -s -XPOST "localhost:9200/exps/_doc/1?refresh=true" -H 'Content-Type: application/json' \
  -d '{"host":"a","n":5,"o":{"a":"x","b":5},"oa":[{"a":"x"},{"a":"y"}],"fo":{"a":"x"},"nd":[{"a":"x"},{"a":"y"}]}'

# 1. A scalar returns HTTP 500 UNNEST argument must be a collection
curl -s -XPOST "localhost:9200/_plugins/_ppl" -H 'Content-Type: application/json' \
  -d '{"query":"source=exps | expand n | fields host"}' -w '\nHTTP %{http_code}\n'

# 2. An object returns HTTP 200 with one row per key in KEY and VALUE columns
curl -s -XPOST "localhost:9200/_plugins/_ppl" -H 'Content-Type: application/json' \
  -d '{"query":"source=exps | fields host, o | expand o"}' -w '\nHTTP %{http_code}\n'

# 3. A flat_object returns the same KEY and VALUE split
curl -s -XPOST "localhost:9200/_plugins/_ppl" -H 'Content-Type: application/json' \
  -d '{"query":"source=exps | fields host, fo | expand fo"}' -w '\nHTTP %{http_code}\n'

# 4. An object holding an array returns HTTP 500 ClassCastException
curl -s -XPOST "localhost:9200/_plugins/_ppl" -H 'Content-Type: application/json' \
  -d '{"query":"source=exps | fields host, oa | expand oa"}' -w '\nHTTP %{http_code}\n'

# 5. A nested field expands, one row per element
curl -s -XPOST "localhost:9200/_plugins/_ppl" -H 'Content-Type: application/json' \
  -d '{"query":"source=exps | fields host, nd | expand nd"}' -w '\nHTTP %{http_code}\n'

Impact

expand on the wrong field is an easy mistake. OpenSearch has no array mapping, so a field that holds arrays in some documents is mapped as a scalar. Dynamic mapping also maps an array of objects as object and not nested, so the usual shape for a list of objects hits the ClassCastException. The user gets a 500 in Java or Calcite wording that names neither the field nor its type, or a 200 with rows split by key.

Suggested fix

In visitExpand, before line 4462, reject any field whose type is not an array with a SemanticCheckException naming the field and its type. That class maps to 400 through QueryEngineException. Rejecting MAP covers object, flat_object and an object holding an array, since all three are typed as a map at analysis. Checking in visitExpand keeps mvexpand and the other caller of buildExpandRelNode at line 1238 unchanged.

Environment Information

OpenSearch Version: main at 4d9839398, 3.10.0-SNAPSHOT, one node, with the defaults plugins.calcite.enabled=true, plugins.calcite.pushdown.enabled=true and plugins.calcite.fallback.allowed=false.

Additional Details:

• #5065 reports the same exception for a scalar field holding an array value. This issue covers single values and object and flat_object fields as well.