[BUG] PPL query with `mvindex()` fails when `plugins.calcite.pushdown.enabled=true`

[mcieno]

Describe the bug

Calcite pushdown mistakes int(1) for long(1), causing the following PPL query to fail with a compile exception (no applicable constructor/method found).

source = my-index | eval e = mvindex(entity, 1) | stats count() by e

CompileException[Line 7, Column 113: No applicable constructor/method found for actual parameters "java.util.List, long, int, boolean"; candidates are: "public static java.lang.Object org.apache.calcite.runtime.SqlFunctions.arrayItemOptional(java.util.List, int, int, boolean)"]

To Reproduce

Prepare the index

Make sure Calcite pushdown is enabled (this should be by default):

PUT /_cluster/settings
{
  "transient": {
    "plugins.calcite.enabled": true,
    "plugins.calcite.pushdown.enabled": true
  }
}

Create an index with a nested property:

PUT /my-index
{
  "mappings": {
    "properties": {
      "entity": {
        "type": "nested",
        "properties": {
          "k":  { "type": "keyword" },
          "v": { "type": "keyword" }
        }
      }
    }
  }
}

Insert a document in the index:

POST /my-index/_doc
{
  "entity": [
    {"k": "foo", "v": "bar"},
    {"k": "baz", "v": "qux"}
  ]
}

PPL query that succeeds

Count documents grouping by the first value of the entity array:

POST /_plugins/_ppl
{
  "query": "source = my-index | eval e = mvindex(entity, 0) | stats count() by e"
}

This query succeeds and returns:

{
  "schema": [
    {
      "name": "count()",
      "type": "bigint"
    },
    {
      "name": "e",
      "type": "string"
    }
  ],
  "datarows": [
    [
      1,
      "{v=bar, k=foo}"
    ]
  ],
  "total": 1,
  "size": 1
}

PPL query that fails

Count the documents grouping by the second value of the entity array:

POST /_plugins/_ppl
{
  "query": "source = my-index | eval e = mvindex(entity, 1) | stats count() by e"
}

This second query fails with:

{
  "error": {
    "context": {
      "stage": "executing",
      "stage_description": "Running the query"
    },
    "reason": "java.sql.SQLException: exception while executing query: Failed to fetch data from the index: the background task failed or interrupted. Inner error: Failed to execute phase [query], all shards failed; shardFailures {[exH8iJ_qT02hXzqDhJ2-ig][my-index][0]: RemoteTransportException[[demo-node][172.19.0.2:9300][indices:data/read/search[phase/query]]]; nested: GeneralScriptException[Failed to compile inline script [{\"langType\":\"calcite\",\"script\":\"rO0ABXQDB3sKICAib3AiOiB7CiAgICAibmFtZSI6ICJJVEVNIiwKICAgICJraW5kIjogIklURU0iLAogICAgInN5bnRheCI6ICJTUEVDSUFMIgogIH0sCiAgIm9wZXJhbmRzIjogWwogICAgewogICAgICAiZHluYW1pY1BhcmFtIjogMCwKICAgICAgInR5cGUiOiB7CiAgICAgICAgInR5cGUiOiAiQVJSQVkiLAogICAgICAgICJudWxsYWJsZSI6IHRydWUsCiAgICAgICAgImNvbXBvbmVudCI6IHsKICAgICAgICAgICJ0eXBlIjogIkFOWSIsCiAgICAgICAgICAibnVsbGFibGUiOiB0cnVlLAogICAgICAgICAgInByZWNpc2lvbiI6IC0xLAogICAgICAgICAgInNjYWxlIjogLTIxNDc0ODM2NDgKICAgICAgICB9CiAgICAgIH0KICAgIH0sCiAgICB7CiAgICAgICJvcCI6IHsKICAgICAgICAibmFtZSI6ICIrIiwKICAgICAgICAia2luZCI6ICJQTFVTIiwKICAgICAgICAic3ludGF4IjogIkJJTkFSWSIKICAgICAgfSwKICAgICAgIm9wZXJhbmRzIjogWwogICAgICAgIHsKICAgICAgICAgICJkeW5hbWljUGFyYW0iOiAxLAogICAgICAgICAgInR5cGUiOiB7CiAgICAgICAgICAgICJ0eXBlIjogIkJJR0lOVCIsCiAgICAgICAgICAgICJudWxsYWJsZSI6IHRydWUKICAgICAgICAgIH0KICAgICAgICB9LAogICAgICAgIHsKICAgICAgICAgICJkeW5hbWljUGFyYW0iOiAyLAogICAgICAgICAgInR5cGUiOiB7CiAgICAgICAgICAgICJ0eXBlIjogIkJJR0lOVCIsCiAgICAgICAgICAgICJudWxsYWJsZSI6IHRydWUKICAgICAgICAgIH0KICAgICAgICB9CiAgICAgIF0KICAgIH0KICBdCn0=\"}] using lang [opensearch_compounded_script]]; nested: RuntimeException[While compiling generated Rex code]; nested: CompileException[Line 7, Column 113: No applicable constructor/method found for actual parameters \"java.util.List, long, int, boolean\"; candidates are: \"public static java.lang.Object org.apache.calcite.runtime.SqlFunctions.arrayItemOptional(java.util.List, int, int, boolean)\"]; }",
    "details": "java.sql.SQLException: exception while executing query: Failed to fetch data from the index: the background task failed or interrupted. Inner error: Failed to execute phase [query], all shards failed; shardFailures {[exH8iJ_qT02hXzqDhJ2-ig][my-index][0]: RemoteTransportException[[demo-node][172.19.0.2:9300][indices:data/read/search[phase/query]]]; nested: GeneralScriptException[Failed to compile inline script [{\"langType\":\"calcite\",\"script\":\"rO0ABXQDB3sKICAib3AiOiB7CiAgICAibmFtZSI6ICJJVEVNIiwKICAgICJraW5kIjogIklURU0iLAogICAgInN5bnRheCI6ICJTUEVDSUFMIgogIH0sCiAgIm9wZXJhbmRzIjogWwogICAgewogICAgICAiZHluYW1pY1BhcmFtIjogMCwKICAgICAgInR5cGUiOiB7CiAgICAgICAgInR5cGUiOiAiQVJSQVkiLAogICAgICAgICJudWxsYWJsZSI6IHRydWUsCiAgICAgICAgImNvbXBvbmVudCI6IHsKICAgICAgICAgICJ0eXBlIjogIkFOWSIsCiAgICAgICAgICAibnVsbGFibGUiOiB0cnVlLAogICAgICAgICAgInByZWNpc2lvbiI6IC0xLAogICAgICAgICAgInNjYWxlIjogLTIxNDc0ODM2NDgKICAgICAgICB9CiAgICAgIH0KICAgIH0sCiAgICB7CiAgICAgICJvcCI6IHsKICAgICAgICAibmFtZSI6ICIrIiwKICAgICAgICAia2luZCI6ICJQTFVTIiwKICAgICAgICAic3ludGF4IjogIkJJTkFSWSIKICAgICAgfSwKICAgICAgIm9wZXJhbmRzIjogWwogICAgICAgIHsKICAgICAgICAgICJkeW5hbWljUGFyYW0iOiAxLAogICAgICAgICAgInR5cGUiOiB7CiAgICAgICAgICAgICJ0eXBlIjogIkJJR0lOVCIsCiAgICAgICAgICAgICJudWxsYWJsZSI6IHRydWUKICAgICAgICAgIH0KICAgICAgICB9LAogICAgICAgIHsKICAgICAgICAgICJkeW5hbWljUGFyYW0iOiAyLAogICAgICAgICAgInR5cGUiOiB7CiAgICAgICAgICAgICJ0eXBlIjogIkJJR0lOVCIsCiAgICAgICAgICAgICJudWxsYWJsZSI6IHRydWUKICAgICAgICAgIH0KICAgICAgICB9CiAgICAgIF0KICAgIH0KICBdCn0=\"}] using lang [opensearch_compounded_script]]; nested: RuntimeException[While compiling generated Rex code]; nested: CompileException[Line 7, Column 113: No applicable constructor/method found for actual parameters \"java.util.List, long, int, boolean\"; candidates are: \"public static java.lang.Object org.apache.calcite.runtime.SqlFunctions.arrayItemOptional(java.util.List, int, int, boolean)\"]; }",
    "location": [
      "while running the query"
    ],
    "code": "UNKNOWN",
    "type": "RuntimeException"
  },
  "status": 500
}

Expected behavior

The second PPL query should succeed too:

POST /_plugins/_ppl
{
  "query": "source = my-index | eval e = mvindex(entity, 1) | stats count() by e"
}

{
  "schema": [
    {
      "name": "count()",
      "type": "bigint"
    },
    {
      "name": "e",
      "type": "string"
    }
  ],
  "datarows": [
    [
      1,
      "{v=qux, k=baz}"
    ]
  ],
  "total": 1,
  "size": 1
}

Additional Details

• Using mvindex(entity, cast(1 as int)) does not fix the problem.
• The query succeeds if I set plugins.calcite.pushdown.enabled to false.

Here's the explain of the non-failing query

// explain source = my-index | eval e = mvindex(entity, 0) | stats count() by e
{
  "calcite": {
    "logical": "LogicalSystemLimit(fetch=[10000], type=[QUERY_SIZE_LIMIT])\n  LogicalProject(count()=[$1], e=[$0])\n    LogicalAggregate(group=[{0}], count()=[COUNT()])\n      LogicalProject(e=[ITEM($0, 1)])\n        CalciteLogicalIndexScan(table=[[OpenSearch, my-index]])\n",
    "physical": "CalciteEnumerableIndexScan(table=[[OpenSearch, my-index]], PushDownContext=[[AGGREGATION->rel#615:LogicalAggregate.NONE.[](input=RelSubset#614,group={0},count()=COUNT()), PROJECT->[count(), e], LIMIT->10000], OpenSearchRequestBuilder(sourceBuilder={\"from\":0,\"size\":0,\"timeout\":\"1m\",\"aggregations\":{\"composite_buckets\":{\"composite\":{\"size\":10000,\"sources\":[{\"e\":{\"terms\":{\"script\":{\"source\":\"{\\\"langType\\\":\\\"calcite\\\",\\\"script\\\":\\\"rO0ABXQB2HsKICAib3AiOiB7CiAgICAibmFtZSI6ICJJVEVNIiwKICAgICJraW5kIjogIklURU0iLAogICAgInN5bnRheCI6ICJTUEVDSUFMIgogIH0sCiAgIm9wZXJhbmRzIjogWwogICAgewogICAgICAiZHluYW1pY1BhcmFtIjogMCwKICAgICAgInR5cGUiOiB7CiAgICAgICAgInR5cGUiOiAiQVJSQVkiLAogICAgICAgICJudWxsYWJsZSI6IHRydWUsCiAgICAgICAgImNvbXBvbmVudCI6IHsKICAgICAgICAgICJ0eXBlIjogIkFOWSIsCiAgICAgICAgICAibnVsbGFibGUiOiB0cnVlLAogICAgICAgICAgInByZWNpc2lvbiI6IC0xLAogICAgICAgICAgInNjYWxlIjogLTIxNDc0ODM2NDgKICAgICAgICB9CiAgICAgIH0KICAgIH0sCiAgICB7CiAgICAgICJkeW5hbWljUGFyYW0iOiAxLAogICAgICAidHlwZSI6IHsKICAgICAgICAidHlwZSI6ICJJTlRFR0VSIiwKICAgICAgICAibnVsbGFibGUiOiB0cnVlCiAgICAgIH0KICAgIH0KICBdCn0=\\\"}\",\"lang\":\"opensearch_compounded_script\",\"params\":{\"utcTimestamp\":1785226288501529922,\"SOURCES\":[1,2],\"DIGESTS\":[\"entity\",1]}},\"missing_bucket\":true,\"missing_order\":\"first\",\"order\":\"asc\"}}}]}}}}, requestedTotalSize=10000, pageSize=null, startFrom=0)])\n"
  }
}

Here's the explain of the failing query

// explain source = my-index | eval e = mvindex(entity, 1) | stats count() by e
{
  "calcite": {
    "logical": "LogicalSystemLimit(fetch=[10000], type=[QUERY_SIZE_LIMIT])\n  LogicalProject(count()=[$1], e=[$0])\n    LogicalAggregate(group=[{0}], count()=[COUNT()])\n      LogicalProject(e=[ITEM($0, +(1, 1))])\n        CalciteLogicalIndexScan(table=[[OpenSearch, my-index]])\n",
    "physical": "CalciteEnumerableIndexScan(table=[[OpenSearch, my-index]], PushDownContext=[[AGGREGATION->rel#698:LogicalAggregate.NONE.[](input=RelSubset#697,group={0},count()=COUNT()), PROJECT->[count(), e], LIMIT->10000], OpenSearchRequestBuilder(sourceBuilder={\"from\":0,\"size\":0,\"timeout\":\"1m\",\"aggregations\":{\"composite_buckets\":{\"composite\":{\"size\":10000,\"sources\":[{\"e\":{\"terms\":{\"script\":{\"source\":\"{\\\"langType\\\":\\\"calcite\\\",\\\"script\\\":\\\"rO0ABXQDB3sKICAib3AiOiB7CiAgICAibmFtZSI6ICJJVEVNIiwKICAgICJraW5kIjogIklURU0iLAogICAgInN5bnRheCI6ICJTUEVDSUFMIgogIH0sCiAgIm9wZXJhbmRzIjogWwogICAgewogICAgICAiZHluYW1pY1BhcmFtIjogMCwKICAgICAgInR5cGUiOiB7CiAgICAgICAgInR5cGUiOiAiQVJSQVkiLAogICAgICAgICJudWxsYWJsZSI6IHRydWUsCiAgICAgICAgImNvbXBvbmVudCI6IHsKICAgICAgICAgICJ0eXBlIjogIkFOWSIsCiAgICAgICAgICAibnVsbGFibGUiOiB0cnVlLAogICAgICAgICAgInByZWNpc2lvbiI6IC0xLAogICAgICAgICAgInNjYWxlIjogLTIxNDc0ODM2NDgKICAgICAgICB9CiAgICAgIH0KICAgIH0sCiAgICB7CiAgICAgICJvcCI6IHsKICAgICAgICAibmFtZSI6ICIrIiwKICAgICAgICAia2luZCI6ICJQTFVTIiwKICAgICAgICAic3ludGF4IjogIkJJTkFSWSIKICAgICAgfSwKICAgICAgIm9wZXJhbmRzIjogWwogICAgICAgIHsKICAgICAgICAgICJkeW5hbWljUGFyYW0iOiAxLAogICAgICAgICAgInR5cGUiOiB7CiAgICAgICAgICAgICJ0eXBlIjogIkJJR0lOVCIsCiAgICAgICAgICAgICJudWxsYWJsZSI6IHRydWUKICAgICAgICAgIH0KICAgICAgICB9LAogICAgICAgIHsKICAgICAgICAgICJkeW5hbWljUGFyYW0iOiAyLAogICAgICAgICAgInR5cGUiOiB7CiAgICAgICAgICAgICJ0eXBlIjogIkJJR0lOVCIsCiAgICAgICAgICAgICJudWxsYWJsZSI6IHRydWUKICAgICAgICAgIH0KICAgICAgICB9CiAgICAgIF0KICAgIH0KICBdCn0=\\\"}\",\"lang\":\"opensearch_compounded_script\",\"params\":{\"utcTimestamp\":1785226356722900040,\"SOURCES\":[1,2,2],\"DIGESTS\":[\"entity\",1,1]}},\"missing_bucket\":true,\"missing_order\":\"first\",\"order\":\"asc\"}}}]}}}}, requestedTotalSize=10000, pageSize=null, startFrom=0)])\n"
  }
}

[RyanL1997]

Thanks for the detailed report — reproduced on latest main.

RCA details

The problem doesn't appear to be in mvindex itself, but in how the expression is serialized when it gets pushed down as a script.

mvindex(arr, n) lowers to Calcite's ITEM, which is 1-based, so the index becomes n + 1. The plan itself looks correct (ITEM($0, +(1, 1)), INTEGER index). During script pushdown, however, numeric operands of arithmetic calls are widened to BIGINT — which is generally needed, since doc values come back as Long. The generated code then computes the index as a long, while the runtime ITEM helper only accepts an int, hence the No applicable constructor/method found ... (java.util.List, long, int, boolean) compile error.

That also explains the two things you noticed:

• mvindex(entity, 0) works — 0 + 1 gets constant-folded into a plain literal before pushdown, so no arithmetic survives to be widened. Any non-zero literal index still carries the +, and fails.
• cast(1 as int) doesn't help — the widening happens at serialization time, after the plan is built, so it's not something the query can influence.
• plugins.calcite.pushdown.enabled=false works — no script is generated at all on that path.

So this likely isn't specific to mvindex: any ITEM-style operator with arithmetic in its index argument would be affected the same way.

Proposed direction

Our current thinking is to fix this at the serialization layer rather than in mvindex: keep the existing widening (it's there for a reason), but cast the arithmetic result back to its declared type, so operators that require a narrow index still receive one. Early experiments look promising — the reported query returns the expected rows, and serializer round-trip and explain-plan coverage stay green — but we still want to validate a few more cases (e.g. a non-literal index such as mvindex(arr, some_int_field)) before settling on the final shape. Simply disabling the widening in this context is not viable — it regresses pushdown for comparisons nested under AND/OR.

A narrower alternative would be to avoid emitting the arithmetic for constant indices during mvindex lowering, but that would only cover literal indices, so we're leaning toward the serializer-level fix.

cc @AjimelecGonzalez