Optimize type casting workaround in addtotals/addcoltotals aggregation logic

[coderabbitai]

Description

A workaround was implemented in PR #4754 to handle type casting issues when performing UNION operations between original data and aggregated totals in the addtotals/addcoltotals commands.

Current Implementation

The method getAggregateDataTypeFieldRef (line 2529 in CalciteRelNodeVisitor.java) explicitly casts:

• INTEGER → BIGINT
• FLOAT/REAL → DOUBLE

This workaround prevents ClassCastException that occurs during UNION operations when Calcite automatically converts aggregation result types.

Problem

When combining original data (with int/float types) and aggregated totals (Calcite converts to long/double), a ClassCastException occurs in multi-node scenarios:

java.lang.ClassCastException: class java.lang.Integer cannot be cast to class java.lang.Long
  at org.apache.calcite.avatica.util.AbstractCursor$LongAccessor.getLong
  at org.opensearch.sql.opensearch.util.JdbcOpenSearchDataTypeConvertor.getExprValueFromSqlType

This issue only manifests in CrossClusterSearchIT tests when multi-node results are combined.

Expected Optimization

Investigate whether:

1. This is a Calcite bug that should be reported upstream
2. There's a better way to handle type compatibility in UNION operations
3. The type conversion can be handled more elegantly at a different layer

Context

• Introduced in: PR Feature addtotals and addcoltotals #4754
• Discussion: Feature addtotals and addcoltotals #4754 (comment)
• Test case demonstrating the issue: https://github.com/asifabashar/sql/pull/8/files

Related Code

core/src/main/java/org/opensearch/sql/calcite/CalciteRelNodeVisitor.java:

• getAggregateDataTypeFieldRef method (line 2529)
• buildAddRowTotalAggregate method usage