[BUG] PPL query fails when using existing field name in `as` clause

[ruanyl]

What is the bug?
A PPL query fails to execute when using an existing field name after AS in the STATS command.

How can one reproduce the bug?

source=opensearch_dashboards_sample_data_logs | stats count() as request by `machine.os`

request is an existing field of opensearch_dashboards_sample_data_logs

What is the expected behavior?
The query should execute successfully and rename the count aggregation to request

[yuancu]

I tested with the latest implementations on v2 and Calcite, stats() as an existing name works without problems.
With Calcite enabled & pushdown disabled this is not a problem.

The query source=bank | stats count() as account_number gives result as:

{
    "schema": [
        {
            "name": "account_number",
            "type": "bigint"
        }
    ],
    "datarows": [
        [
            7
        ]
    ],
    "total": 1,
    "size": 1
}

The schema has account_number as a field:

{
  "mappings": {
    "properties": {
      "account_number": {
        "type": "long"
      },
      "address": {
        "type": "text"
      },
      "age": {
        "type": "integer"
      },
      "balance": {
        "type": "long"
      },
      "birthdate": {
        "type": "date"
      },
      "city": {
        "type": "keyword"
      },
      "email": {
        "type": "text"
      },
      "employer": {
        "type": "text"
      },
      "firstname": {
        "type": "keyword"
      },
      "gender": {
        "type": "text",
        "fielddata": true
      },
      "lastname": {
        "type": "keyword"
      },
      "male": {
        "type": "boolean"
      },
      "state": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      }
    }
  }
}

UPDATE: The problem arises when the field type in the table isn't compatible with the stats result.

With calcite disabled: source=bank | stats count() as lastname will throw an error as follows:

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

With Calcite enabled and pushdown disabled this is not a problem.

[LantaoJin]

this issue should be resolved in v3

[qianheng-aws]

#4500 will solve this issue, both for v2 and v3