SQL: Add support for NULL ordering (when grouping)

[costin]

Currently, due to restrictions in the query DSL (#34221), null cannot be placed first or last regardless of the order as they obey the order (first if ASC, last if DESC).
This option is supported when searching without grouping though.

Once the fix lands in ES, SQL should follow suit.

[elasticmachine]

Pinging @elastic/es-search-aggs

[Luegg]

I also had a look at whether the same approach as suggested in #34597 (comment) (using an additional sort column for nullness) can be used for aggregations and it seems to be a possibility as well:

E.g. the query select lcase(gender) g, max(languages) from test_emp group by g order by g asc nulls last can be translated to something like:

{
    "size": 0,
    "_source": false,
    "aggregations": {
        "groupby": {
            "composite": {
                "size": 1000,
                "sources": [
                    {
                        "a0209299": {
                            "terms": {
                                "script": {
                                    "source": "InternalQlScriptUtils.nullSafeFilter(InternalQlScriptUtils.isNull(InternalSqlScriptUtils.lcase(InternalQlScriptUtils.docValue(doc,params.v0)))) ? params.v1 : params.v2",
                                    "lang": "painless",
                                    "params": {
                                        "v0": "gender",
                                        "v1": true,
                                        "v2": false
                                    }
                                },
                                "missing_bucket": true,
                                "value_type": "boolean",
                                "order": "asc"
                            }
                        }
                    },
                    {
                        "290dcd6a": {
                            "terms": {
                                "script": {
                                    "source": "InternalSqlScriptUtils.lcase(InternalQlScriptUtils.docValue(doc,params.v0))",
                                    "lang": "painless",
                                    "params": {
                                        "v0": "gender"
                                    }
                                },
                                "missing_bucket": true,
                                "value_type": "string",
                                "order": "asc"
                            }
                        }
                    }
                ]
            },
            "aggregations": {
                "e853faa0": {
                    "max": {
                        "field": "languages"
                    }
                }
            }
        }
    }
}

Since InternalQlScriptUtils.isNull(expr) only changes with the value of expr, the second grouping does not really add another dimension and the two dimensions can always be mapped back to the expr dimension.

This approach is also compatible with derived values and scrolling.