[BUG] aggregation works incorrectly with LIMIT

[Yury-Fridlyand]

What is the bug?
LIMIT applied incorrectly in query with aggregation.

How can one reproduce the bug?
Steps to reproduce the behavior:
Run any query with aggregation which returns more than 200 rows. Try adding LIMIT clause.
For example:

select count(*) from online group by all_client / 100 limit 2000

(index online is taken from IT)

Physical plan tree is being executed:

What is the expected behavior?
LIMIT rule should be applied correctly for such queries.
Probably pushDownLimit should be rejected if query has aggregation.

What is your host/environment?
main @ 94d5479

Do you have any screenshots?
Customer request: https://opensearch.slack.com/archives/C051JEH8MNU/p1686871225569409

Do you have any additional context?
Related issues: #716, #1764, #1752

[robertbrowncc]

Is there any indication when the LIMIT issue will be resolved? I am having the same problem as well. Normal queries and such I can go up to any number. However anything with a group by in it is stuck at 200 or lower, the lower values do work.

[robertbrowncc]

Found a work around!

If you are experiencing this issue, aggregates/group by is causing and limiting results to only 200 max.

Don't run the query but instead do an explain on your SQL attempt.

The results of the explain will show you the standard opensearch query that would have been based on your SQL.

Get this out and clean it up, then drop back to the normal opensearch query method.

This will then run as intended and return the full set of "buckets" not just 200.

[KingRabbid]

One solution is to increase the limit for the SQL plugins at cluster level; details here: https://opensearch.org/docs/2.12/search-plugins/sql/settings/, adjust parameter: plugins.query.size_limit.