Support _first and _last parameter for missing bucket ordering in composite aggregation

[penghuo]

Is your feature request related to a problem? Please describe.
If OpenSearch composite aggregation's missing_bucket is true, null bucket will be included. But user can't control missing_bucket's position in result. Currently, if order is asc, missing bucket is at first ASC NULLS FIRST, if oder is desc, missing bucket is at last DESC NULLS LAST.
In SQL, user could use NULLS FIRST/NULLS LAST to set null value's position regardless order. for example, result is ordered by gender in DESC order, null values are at last.
SELECT gender, avg(age) FROM accounts GROUP BY gender ORDER BY gender DESC NULLS LAST

Without support missing_bucket's position, OpenSearch SQL can not optimize SQL query when user use ASC NULLS_LAST/DESC NULLS_FIRST. More detail in here.

Describe the solution you'd like
Add new parameter missing_order in composite aggregation. Accepted order are _first and _last. Compatible with field sort parameter.

{
  "size": 0,
  "aggs": {
    "my_buckets": {
      "composite": {
        "sources": [
          {
            "product_name": {
              "terms": {
                "field": "product",
                "missing_bucket": true,
                "missing_order": "_last",
                "order": "asc"
              }
            }
          }
        ]
      }
    }
  }
}

Similar feature in Elasticsearch https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-composite-aggregation.html#_missing_bucket

Describe alternatives you've considered
We could also consider compatible with Elasticsearch missing_order parameter. For example,

{
  "size": 0,
  "aggs": {
    "my_buckets": {
      "composite": {
        "sources": [
          {
            "product_name": {
              "terms": {
                "field": "product",
                "missing_bucket": true,
                "missing_order": "last",
                "order": "asc"
              }
            }
          }
        ]
      }
    }
  }
}

Additional context
Add any other context or screenshots about the feature request here.

[anirudha]

@anasalkouz @nknize could you please help prioritize this review, thanks!

[penghuo]

@penghuo prepare separate backporting PR for 1.3.0.

[dblock]

With #1942 should this be closed? What's left?

[penghuo]

I will post another PR for back porting to 1.x. We are good to close this issue now.

[penghuo]

Adjust version on main after backport to 1.3.0.0 #2147.