[FEATURE] Improve Join query result retrieval for full result sets

[dai-chen]

Is your feature request related to a problem?

Currently, Join queries always return 200 result rows by default. Users have to use the LIMIT clause to adjust the result size. This requires specifying a very large number to retrieve all results, which is cumbersome and inefficient.

What solution would you like?

Potential solutions include:

1. Support for pagination in Join query results.
2. Implement alternative communication protocol than HTTP REST

Either would allow users to retrieve results in manageable chunks, eliminating the need for specifying large LIMIT values and improving overall efficiency and usability.

What alternatives have you considered?

N/A

Do you have any additional context?

1. Code: https://github.com/opensearch-project/sql/blob/main/legacy/src/main/java/org/opensearch/sql/legacy/domain/JoinSelect.java#L30
2. Size limit setting is for how many docs fetched from DSL: https://github.com/opensearch-project/sql/blob/main/docs/user/admin/settings.rst#plugins-query-size-limit