[BUG]Aggregation fails when grouping by timestamp-derived fields

[ahkcs]

Aggregation fails when grouping by timestamp-derived fields

Bug Description

There is an issue in the stats/aggregation logic when grouping by timestamp-related fields. This affects both the bin command with timestamp aggregation and direct timestamp field manipulation.

Steps to Reproduce

Minimal Reproduction Case

source=time_test | eval t = unix_timestamp(@timestamp) | stats count() by t

Original Failing Test Case

source=opensearch-sql_test_index_time_data | bin @timestamp span=4h | stats count() by @timestamp | sort @timestamp | head 3

Current Behavior

Both queries fail with aggregation errors, indicating the problem is in the stats command aggregation logic,

Expected Behavior

• stats count() by t should successfully group by the timestamp-derived field and return aggregated counts
• bin @timestamp span=4h | stats count() by @timestamp should successfully bin timestamps into 4-hour intervals and aggregate counts

Test Case for Verification

@Test
public void testTimestampAggregationIssue() throws IOException {
    // Simple case - should work
    JSONObject result = executeQuery(
        "source=time_test | eval t = unix_timestamp(@timestamp) | stats count() by t"
    );
    
    // Bin case - should also work  
    JSONObject binResult = executeQuery(
        "source=opensearch-sql_test_index_time_data | bin @timestamp span=4h | stats count() by @timestamp"
    );
}

[yuancu]

@timestamp is of type TIMESTAMP, but when it reaches Expression level in UnixTimestampFunction, it is represented as an ExprStringValue instead of ExprTimestampValue.

source=time_test | eval t = unix_timestamp(@timestamp) | stats count() by t works with pushdown disabled. It might have something to do with pushdown.

[Swiddis]

{
  "error": {
    "reason": "Error occurred in OpenSearch engine: all shards failed",
    "details": "Shard[0]: AggregationExecutionException[Unsupported script value [2023-01-01], expected a number, date, or boolean]\
Shard[1]: AggregationExecutionException[Unsupported script value [2023-01-01], expected a number, date, or boolean]\
Shard[2]: AggregationExecutionException[Unsupported script value [2023-01-01], expected a number, date, or boolean]\
Shard[3]: AggregationExecutionException[Unsupported script value [2023-01-01], expected a number, date, or boolean]\
Shard[4]: AggregationExecutionException[Unsupported script value [2023-01-01], expected a number, date, or boolean]\
\
For more details, please send request for Json format to see the raw response from OpenSearch engine.",
    "type": "SearchPhaseExecutionException"
  },
  "status": 500
}

The error implies that a date should work, but this error comes after casting:

source=big5
| sort `@timestamp` 
| fields `@timestamp`, process.name 
| eval logd=cast(`@timestamp` AS date) 
| stats count() by logd, process.name 
| head;