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

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

Reproduce the failure through the _plugins/_ppl endpoint with Calcite and pushdown enabled, comparing mvindex(entity, 0) with mvindex(entity, 1). Inspect the Calcite pushdown path that generates ITEM($0, +(1, 1)) and the resulting arrayItemOptional call. Done means the second query succeeds and returns the expected second nested entity while the existing query continues to work.