PPL: avg() on DATE/TIMESTAMP field fails instead of implicitly casting to numeric

[ahkcs]

Description

When using avg() on a DATE/TIMESTAMP field followed by arithmetic operations and ROUND(), the query fails with an ExpressionEvaluationException. The avg() aggregation rejects DATE/TIMESTAMP fields upfront because it only accepts NUMERIC operand types.

Failing Query

source=opensearch_dashboards_sample_data_logs
| stats count() as Request_Count, avg(`response`) as avg_response by `response`
| eval success_rate = (avg_response/Request_Count)
| fields success_rate, Request_Count, response
| eval rounded_success_rate = ROUND(success_rate, 2)
| fields rounded_success_rate

Error

{
  "error": {
    "reason": "Invalid Query",
    "details": "Aggregation function AVG expects field type {[INTEGER]|[DOUBLE]}, but got [TIMESTAMP]",
    "type": "ExpressionEvaluationException"
  },
  "status": 400
}

Root Cause

NullableSqlAvgAggFunction uses OperandTypes.NUMERIC which rejects DATE/TIMESTAMP fields. The avg() call fails before any downstream arithmetic is reached.

Key Files

• core/src/main/java/org/opensearch/sql/calcite/udf/udaf/NullableSqlAvgAggFunction.java — avg() operand validation
• core/src/main/java/org/opensearch/sql/expression/function/PPLFuncImpTable.java — avg() registration

Proposed Fix

Extend avg() to accept DATE/TIMESTAMP fields by implicitly casting them to BIGINT (epoch millis) before averaging, so the result is numeric (DOUBLE). This allows downstream arithmetic and ROUND() to work naturally.

[ahkcs]

Root Cause Analysis

Tested the failing query on both V2 and Calcite engines against the opensearch-sql_test_index_bank index (which has a birthdate field of TIMESTAMP type):

source=opensearch-sql_test_index_bank | stats avg(birthdate) as avg_bd, count() as cnt | eval rate = avg_bd / cnt

V2 Engine

{
  "error": {
    "reason": "Invalid Query",
    "details": "/ function expected {[BYTE,BYTE],[SHORT,SHORT],[INTEGER,INTEGER],[LONG,LONG],[FLOAT,FLOAT],[DOUBLE,DOUBLE]}, but got [TIMESTAMP,LONG]",
    "type": "ExpressionEvaluationException"
  },
  "status": 400
}

avg(birthdate) succeeds and returns a TIMESTAMP type, but the downstream division (avg_bd / cnt) fails because / does not accept [TIMESTAMP, LONG].

Calcite Engine

{
  "error": {
    "reason": "Invalid Query",
    "details": "Aggregation function AVG expects field type {[INTEGER]|[DOUBLE]}, but got [TIMESTAMP]",
    "type": "ExpressionEvaluationException"
  },
  "status": 400
}

avg() rejects the TIMESTAMP field upfront — its type checker (OperandTypes.NUMERIC) only accepts numeric types.

Summary

Engine	Where it fails	Error
V2	eval rate = avg_bd / cnt (division)	/ does not accept [TIMESTAMP, LONG]
Calcite	stats avg(birthdate) (aggregation)	AVG does not accept [TIMESTAMP]

Both engines fail on this query, but at different stages. The Calcite engine fails earlier with a clearer error message.

Note: The original failing query uses avg(\response`)— we need to confirm whether theresponsefield in the customer'sopensearch_dashboards_sample_data_logsindex is actually mapped as TIMESTAMP (e.g., auto-detected as epoch millis due to numeric values). If so, the real question is whetheravg()` should implicitly cast DATE/TIMESTAMP to numeric, or if the index mapping should be corrected.