[BUG] `HAVING` filters are silently ignored on bucketless aggregations in Legacy

What is the bug?

In Legacy, when supplying a HAVING clause with no GROUP BY, there's no bucket_filter applied on the resulting aggregation:

$ echo '{
    "query": "SELECT AVG(metrics.size) AS avg_bytes FROM big5 HAVING AVG(metrics.size) > 20000;",
    "format": "jdbc"
}' | xh post 'localhost:9200/_plugins/_sql/_explain'

HTTP/1.1 200 OK
Content-Encoding: gzip
Content-Length: 125
Content-Type: application/json; charset=UTF-8
X-Opensearch-Version: OpenSearch/3.3.0-SNAPSHOT (opensearch)

{
    "from": 0,
    "size": 0,
    "_source": {
        "includes": [
            "AVG"
        ],
        "excludes": []
    },
    "aggregations": {
        "avg_bytes": {
            "avg": {
                "field": "metrics.size"
            }
        }
    }
}

Compare to supplying a grouping:

$ echo '{
    "query": "SELECT AVG(metrics.size) AS avg_bytes FROM big5 GROUP BY event.id HAVING AVG(metrics.size) > 20000;",
    "format": "jdbc"
}' | xh post 'localhost:9200/_plugins/_sql/_explain'

HTTP/1.1 200 OK
Content-Encoding: gzip
Content-Length: 308
Content-Type: application/json; charset=UTF-8
X-Opensearch-Version: OpenSearch/3.3.0-SNAPSHOT (opensearch)

{
    "from": 0,
    "size": 0,
    "_source": {
        "includes": [
            "AVG"
        ],
        "excludes": []
    },
    "aggregations": {
        "event.id.keyword": {
            "terms": {
                "field": "event.id.keyword",
                "size": 200,
                "min_doc_count": 1,
                "shard_min_doc_count": 0,
                "show_term_doc_count_error": false,
                "order": [
                    {
                        "_count": "desc"
                    },
                    {
                        "_key": "asc"
                    }
                ]
            },
            "aggregations": {
                "avg_bytes": {
                    "avg": {
                        "field": "metrics.size"
                    }
                },
                "avg_0": {
                    "avg": {
                        "field": "metrics.size"
                    }
                },
                "bucket_filter": {
                    "bucket_selector": {
                        "buckets_path": {
                            "avg_bytes": "avg_bytes",
                            "avg_0": "avg_0"
                        },
                        "script": {
                            "source": "params.avg_0 > 20000",
                            "lang": "painless"
                        },
                        "gap_policy": "skip"
                    }
                }
            }
        }
    }
}

Or even a constant grouping:

$ echo '{
    "query": "SELECT AVG(metrics.size) AS avg_bytes FROM big5 GROUP BY 0.0 HAVING AVG(metrics.size) > 20000;",
    "format": "jdbc"
}' | xh post 'localhost:9200/_plugins/_sql/_explain'

HTTP/1.1 200 OK
Content-Encoding: gzip
Content-Length: 326
Content-Type: application/json; charset=UTF-8
X-Opensearch-Version: OpenSearch/3.3.0-SNAPSHOT (opensearch)

{
    "from": 0,
    "size": 0,
    "_source": {
        "includes": [
            "AVG"
        ],
        "excludes": []
    },
    "aggregations": {
        "assign_1": {
            "terms": {
                "script": {
                    "source": "def assign_2 = 0.0;return assign_2;",
                    "lang": "painless"
                },
                "size": 200,
                "min_doc_count": 1,
                "shard_min_doc_count": 0,
                "show_term_doc_count_error": false,
                "order": [
                    {
                        "_count": "desc"
                    },
                    {
                        "_key": "asc"
                    }
                ]
            },
            "aggregations": {
                "avg_bytes": {
                    "avg": {
                        "field": "metrics.size"
                    }
                },
                "avg_0": {
                    "avg": {
                        "field": "metrics.size"
                    }
                },
                "bucket_filter": {
                    "bucket_selector": {
                        "buckets_path": {
                            "avg_bytes": "avg_bytes",
                            "avg_0": "avg_0"
                        },
                        "script": {
                            "source": "params.avg_0 > 20000",
                            "lang": "painless"
                        },
                        "gap_policy": "skip"
                    }
                }
            }
        }
    }
}

How can one reproduce the bug?
Steps to reproduce the behavior:

1. Load a few dozen rows of the big5 dataset
2. Try the above queries
3. Observe that the groupless version returns a result, while the rest return no results (avg for all groups is ~2.4k)

What is the expected behavior?
HAVING without GROUP BY is valid in all SQL varieties except Sqlite.

Sqlite and Druid, turns out. Should handle the error: https://github.com/opensearch-project/sql/issues/4432#issuecomment-3362453275

What is your host/environment?

• Mainline

Do you have any screenshots?
N/A

Do you have any additional context?
Doesn't really matter after the Calcite migration, but came up as part of on-call.

Reproduce the issue with the big5 dataset through the _plugins/_sql/_explain endpoint, comparing the bucketless and grouped HAVING queries shown here. Then trace Legacy aggregation handling for bucket_filter and confirm the intended outcome from the latest issue discussion. Done means the groupless HAVING case no longer silently ignores the filter and its behavior matches the clarified expectation.