Elasticsearch SQL aggregation query always return 1,000 results

I'm using elasticsearch built-in SQL for aggregation query, and the maximum number of results I get is always 1,000 - even when I set LIMIT. When I use the translate API, I understand the 1,000 is because the composite aggregation size is 1,000.

There is anyway to change increase that default?

Could you provide some more info, regarding your ES version and the SQL query you're running?

I'm using 7.5.2.
All the aggregation query, for example:
SELECT productName, count(*) as cnt FROM "INDEX_NAME" GROUP BY productName LIMIT 5000

The translation for this SQL to ES query, reveal that the composite size is 1,000 although the LIMIT is 5,000

{
  "size" : 0,
  "_source" : false,
  "stored_fields" : "_none_",
  "aggregations" : {
    "groupby" : {
      "composite" : {
        "size" : 1000,
        "sources" : [
          {
            "46601" : {
              "terms" : {
                "field" : "productName.keyword",
                "missing_bucket" : true,
                "order" : "asc"
              }
            }
          }
        ]
      }
    }
  }
}

The size is controlled by fetch_size parameter:

POST localhost:9200/_sql/translate
{
    "query" : "SELECT productName, count(*) as cnt FROM \"INDEX_NAME\" GROUP BY productName LIMIT 5000",
    "fetch_size": 50
}

and doesn't have to do with the LIMIT set.
With limit 5000 and fetch_size 50 you will still get 5000 rows (if exist) but in 100 pages of 50 rows each. You can find more information about pagination here.