[FEATURE] Support paging system requests

[Yury-Fridlyand]

Is your feature request related to a problem?
If the main customer of SQL plugin -- JDBC driver -- uses pagination, it sends all queries as paging requests. See opensearch-project/sql-jdbc#67.

What solution would you like?
Support pagination of queries like

SHOW TABLES
DESCRIBE TABLES

{
    "query" : "describe tables like 'calcs'",
    "fetch_size" : 5
}

Currently, such request has following response:

{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "'class org.opensearch.sql.opensearch.storage.system.OpenSearchSystemIndex' does not support pagination",
    "type": "UnsupportedOperationException"
  },
  "status": 503
}

Error stack:

Server side error during query execution
java.lang.UnsupportedOperationException: 'class org.opensearch.sql.opensearch.storage.system.OpenSearchSystemIndex' does not support pagination
        at org.opensearch.sql.storage.Table.createPagedScanBuilder(Table.java:105) ~[core-3.0.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.planner.optimizer.rule.CreatePagingTableScanBuilder.apply(CreatePagingTableScanBuilder.java:67) ~[core-3.0.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.planner.optimizer.rule.CreatePagingTableScanBuilder.apply(CreatePagingTableScanBuilder.java:23) ~[core-3.0.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.planner.optimizer.LogicalPlanOptimizer.internalOptimize(LogicalPlanOptimizer.java:85) ~[core-3.0.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.planner.optimizer.LogicalPlanOptimizer.optimize(LogicalPlanOptimizer.java:70) ~[core-3.0.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.planner.Planner.optimize(Planner.java:65) ~[core-3.0.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.planner.Planner.plan(Planner.java:41) ~[core-3.0.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.executor.QueryService.plan(QueryService.java:108) ~[core-3.0.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.executor.QueryService.lambda$executePlan$1(QueryService.java:75) ~[core-3.0.0.0-SNAPSHOT.jar:?]
        at java.util.Optional.ifPresentOrElse(Optional.java:198) ~[?:?]
        at org.opensearch.sql.executor.QueryService.executePlan(QueryService.java:72) [core-3.0.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.executor.QueryService.execute(QueryService.java:43) [core-3.0.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.executor.execution.QueryPlan.execute(QueryPlan.java:71) [core-3.0.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.executor.OpenSearchQueryManager.lambda$submit$0(OpenSearchQueryManager.java:33) [opensearch-3.0.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.executor.OpenSearchQueryManager.lambda$withCurrentContext$1(OpenSearchQueryManager.java:47) [opensearch-3.0.0.0-SNAPSHOT.jar:?]
        at org.opensearch.common.util.concurrent.ThreadContext$ContextPreservingRunnable.run(ThreadContext.java:747) [opensearch-3.0.0-SNAPSHOT.jar:3.0.0-SNAPSHOT]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) [?:?]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) [?:?]
        at java.lang.Thread.run(Thread.java:1589) [?:?]

What alternatives have you considered?
Ignore fetch_size parameter for such queries and always return full response.

Do you have any additional context?
Implementation of that require implementing a new Physical Plan node -- PaginateOperator.
It was implemented in past and then removed in 1f6cf70 in #1497. Paging system query is not supported by legacy engine too.

[qianheng-aws]

V2 engine is no longer maintained. Closed as too staleClosed as too stale. Please reopen or create a new one for calcite engine if it's necessary.