[BUG] Calcite PPL doesn't handle integer array columns

[Swiddis]

Query Information

PPL Command/Query:
Anything matching the problematic fields (e.g. source = test_idx)

Expected Result:
When Calcite is disabled, this returns:

{
  "schema": [
    {
      "name": "nums",
      "type": "bigint"
    }
  ],
  "datarows": [
    [
      [
        1,
        2,
        3
      ]
    ]
  ],
  "total": 1,
  "size": 1
}

Actual Result:

{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "class java.util.ArrayList cannot be cast to class java.lang.Long (java.util.ArrayList and java.lang.Long are in module java.base of loader 'bootstrap')",
    "type": "ClassCastException"
  },
  "status": 500
}

Dataset Information

Dataset/Schema Type

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

Index Mapping

{
  "test_idx": {
    "mappings": {
      "properties": {
        "nums": {
          "type": "long"
        }
      }
    }
  }
}

Sample Data

{ "nums": [1, 2, 3] }

Bug Description

Issue Summary:
Calcite fails to return any results containing a list of integers. This is probably related to the type in OpenSearch for these fields being long, with no indication of whether or not it contains an array.

Workaround: use fields and remove the problematic field.

Steps to Reproduce:

PUT localhost:9200/_cluster/settings
Content-Type: application/json

{
    "transient": {
        "plugins.calcite.enabled": true
    }
}

###
POST localhost:9200/test_idx/_doc
Content-Type: application/json

{ "nums": [1, 2, 3] }

###
POST localhost:9200/_plugins/_ppl
Content-Type: application/json

{
  "query": "source = test_idx"
}

Impact:
Anyone who previously used these fields in queries won't be able to do so on Calcite, though I'm not sure how prevalent they are.

Environment Information

OpenSearch Version:
Mainline (c54acc4)

Additional Details:
N/A

Screenshots

N/A

[LantaoJin]

Thanks @Swiddis for reporting this. This is a known issue. It caused by the limitation of Calcite which is a strong schema system.

How about add a limitation description in doc? I dived into the code and it needed code changes in Calcite internal.

[acarbonetto]

This also fails with head command combined with top. The head and the top commands work in isolation.

Note, audit_trace_resolved_indices is mapped to a text field.

Query:

source=security-auditlog
| where ISPRESENT(audit_trace_resolved_indices)
| fields audit_trace_resolved_indices
| head 100
| top audit_trace_resolved_indices

error:
"{\n  \"error\": {\n    \"reason\": \"There was internal problem at backend\",\n    \"details\": \"java.sql.SQLException: exception while executing query: class java.util.ArrayList cannot be cast to class java.lang.String (java.util.ArrayList and java.lang.String are in module java.base of loader 'bootstrap')\",\n    \"type\": \"RuntimeException\"\n  },\n  \"status\": 500\n}"

Removing the head command works on the first element of the array:

source=security-auditlog-2024.05
| where ISPRESENT(audit_trace_resolved_indices)
| fields audit_trace_indices, audit_trace_resolved_indices
| top 999 audit_trace_resolved_indices

returns:
{
  "schema": [
    {
      "name": "audit_trace_resolved_indices",
      "type": "string"
    },
    {
      "name": "count",
      "type": "bigint"
    }
  ],
  "datarows": [
    [
      "opensearch_dashboards_sample_data_flights",
      143
    ],

Working query:

source=security-auditlog
| where ISPRESENT(audit_trace_resolved_indices)
| where audit_trace_resolved_indices="opensearch_dashboards_sample_data_flights"
| fields audit_trace_indices, audit_trace_resolved_indices
| head 100

returns: 
{
  "schema": [
    {
      "name": "audit_trace_indices",
      "type": "string"
    },
    {
      "name": "audit_trace_resolved_indices",
      "type": "string"
    }
  ],
  "datarows": [
    [
      "[opensearch_dashboards_sample_data_flights]",
      "[opensearch_dashboards_sample_data_flights]"
    ],
    ...
  ],
  "total": 100,
  "size": 100
}

Seems like top after

[acarbonetto]

related to: #3751