[BUG] SQL pagination doesn't work in Dev tools

[dai-chen]

What is the bug?

Exception thrown as below when paging through query result in Dev tools. This is breaking behavior as it was working in OpenSearch 1.3.

After investigation, I found the root cause is V2 pagination implementation restrict URL param different from previous. Since Dev tools sends request with parameter pretty, it fall back to V1 engine which has different cursor encoding and thus throw exception.

Code: https://github.com/opensearch-project/sql/blob/main/sql/src/main/java/org/opensearch/sql/sql/domain/SQLQueryRequest.java#L84

How can one reproduce the bug?

POST _plugins/_sql
{
  "query": "SELECT Origin FROM opensearch_dashboards_sample_data_flights WHERE Origin LIKE '%International%'",
  "fetch_size": 5
}

POST _plugins/_sql
{
  "cursor": "n:1f8b08000000000000ff..."
}
"{\n  \"error\": {\n    \"reason\": \"Invalid SQL query\",\n    
\"details\": \"Cannot invoke \\\"String.replaceAll(String, String)\\\" 
because \\\"sql\\\" is null\",\n    \"type\": \"NullPointerException\"\n  },\n  \"status\": 400\n}"

What is the expected behavior?

SQL pagination can work in Dev tools as before.

What is your host/environment?

• OS: 2.9

Do you have any additional context?

Error log:

[2023-11-13T20:00:43,465][ERROR][o.o.s.l.p.RestSqlAction  ] Client side error during query execution
java.lang.NullPointerException: Cannot invoke "String.replaceAll(String, String)" because "sql" is null
    at org.opensearch.sql.legacy.antlr.OpenSearchLegacySqlAnalyzer.isSelectStatement(OpenSearchLegacySqlAnalyzer.java:102)
    at org.opensearch.sql.legacy.antlr.OpenSearchLegacySqlAnalyzer.analyze(OpenSearchLegacySqlAnalyzer.java:44)
    at org.opensearch.sql.legacy.plugin.RestSqlAction.performAnalysis(RestSqlAction.java:292)
    at org.opensearch.sql.legacy.plugin.RestSqlAction.explainRequest(RestSqlAction.java:217)
    at org.opensearch.sql.legacy.plugin.RestSqlAction.lambda$prepareRequest$1(RestSqlAction.java:161)
    at org.opensearch.sql.legacy.plugin.RestSQLQueryAction.lambda$prepareRequest$0(RestSQLQueryAction.java:92)
    at org.opensearch.rest.BaseRestHandler.handleRequest(BaseRestHandler.java:127)
    ...

[anasalkouz]

@dai-chen I think this #2441 supposed to fix this issue. if yes, could you please close it?

[LantaoJin]

@dai-chen I think this #2441 supposed to fix this issue. if yes, could you please close it?

The issue is different. I am going to provide a patch.