[BUG] Aggregation is pushed down through fields generated by window operator

[songkant-aws]

Query Information

PPL Command/Query:

source=ss4o_logs* | where time>'2025-08-07 03:00:00' and time<'2025-08-07 03:10:00' | patterns body method=brain variable_count_threshold=3 | stats count() as cnt by patterns_field | fields cnt, patterns_field

Expected Result:
PPL query should return valid results.

Actual Result:
{\n "error": {\n "reason": "Error occurred in OpenSearch engine: all shards failed",\n "details": "Shard[0]: java.lang.IllegalStateException: Failed to deserialize RexNode and its required structure: {\n \"op\": {\n \"name\": \"SAFE_CAST\",\n \"kind\": \"SAFE_CAST\",\n \"syntax\": \"SPECIAL\"\n },\n \"operands\": [\n {\n \"op\": {\n \"name\": \"ITEM\",\n \"kind\": \"ITEM\",\n \"syntax\": \"SPECIAL\"\n },\n \"operands\": [\n {\n \"op\": {\n \"name\": \"PATTERN_PARSER\",\n \"kind\": \"OTHER_FUNCTION\",\n \"syntax\": \"FUNCTION\"\n },\n \"operands\": [\n {\n \"input\": 0,\n \"name\": \"$0\"\n },\n {\n \"op\": {\n \"name\": \"pattern\",\n \"kind\": \"OTHER_FUNCTION\",\n \"syntax\": \"FUNCTION\"\n },\n \"operands\": [\n {\n \"input\": 0,\n \"name\": \"$0\"\n },\n {\n \"literal\": 10,\n \"type\": {\n \"type\": \"INTEGER\",\n \"nullable\": false\n }\n },\n {\n \"literal\": 100000,\n \"type\": {\n \"type\": \"INTEGER\",\n \"nullable\": false\n }\n },\n {\n \"literal\": 3,\n \"type\": {\n \"type\": \"INTEGER\",\n \"nullable\": false\n }\n }\n ],\n \"class\": \"org.apache.calcite.sql.validate.SqlUserDefinedAggFunction\",\n \"type\": {\n \"type\": \"ARRAY\",\n \"nullable\": true,\n \"component\": {\n \"type\": \"MAP\",\n \"nullable\": false,\n \"key\": {\n \"type\": \"VARCHAR\",\n \"nullable\": false,\n \"precision\": -1\n },\n \"value\": {\n \"type\": \"ANY\",\n \"nullable\": false,\n \"precision\": -1,\n \"scale\": -2147483648\n }\n }\n },\n \"deterministic\": true,\n \"dynamic\": false,\n \"distinct\": false,\n \"window\": {\n \"range-lower\": {\n \"type\": \"UNBOUNDED_PRECEDING\"\n },\n \"range-upper\": {\n \"type\": \"UNBOUNDED_FOLLOWING\"\n }\n }\n }\n ],\n \"class\": \"org.opensearch.sql.expression.function.UserDefinedFunctionBuilder$1\",\n \"type\": {\n \"type\": \"MAP\",\n \"nullable\": false,\n \"key\": {\n \"type\": \"VARCHAR\",\n \"nullable\": false,\n \"precision\": -1\n },\n \"value\": {\n \"type\": \"ANY\",\n \"nullable\": false,\n \"precision\": -1,\n \"scale\": -2147483648\n }\n },\n \"deterministic\": true,\n \"dynamic\": false\n },\n {\n \"literal\": \"pattern\",\n \"type\": {\n \"type\": \"CHAR\",\n \"nullable\": false,\n \"precision\": 7\n }\n }\n ]\n }\n ],\n \"type\": {\n \"type\": \"VARCHAR\",\n \"nullable\": false,\n \"precision\": -1\n }\n}\n\nFor more details, please send request for Json format to see the raw response from OpenSearch engine.",\n "type": "SearchPhaseExecutionException"\n },\n "status": 500\n}

Dataset Information

Dataset/Schema Type

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

Index Mapping

{
  "mappings": {
    "properties": {
      "field_name": { "type": "type" }
      // Add your index mapping here
      // Replace sensitive field names with generic alternatives
    }
  }
}

Sample Data

{
  // Add sample document that reproduces the issue
  // Use dummy/anonymized data for sensitive fields
  // Example: Replace real IPs with 10.0.0.x, real emails with user@example.com
}

Bug Description

Issue Summary:
OpenSearch PPL allows aggregation pushdown as a script. It matches the logical plan nodes like Aggregation-Project-Scan and transform to a Scan with pushed down agg.

But sometimes the aggregation depends on a field generated by window operator (Calcite logical plan puts window OverCall into a field in Project operator). For now, we'd better avoid pushing down agg over window function column because it's similar to nested aggregate over aggregated result.

Impact:
Affects all agg pushdown over window operator generated column

Environment Information

OpenSearch Version:
3.2

Additional Details:

Screenshots

[songkant-aws]

Looking this one

[songkant-aws]

Not reproduced the issue. It should be already addressed by #4002. Closing this issue.