How to make fetch size work in OpenSearch SQL plug-in?

Question

OpenSearch documentation says that fetch_size is supported and should work. However, this definitely is not working for me in OpenSearch 2.5.3.

Query 1:

POST _plugins/_sql/
{
  "query" : "SELECT start_time FROM my_opensearch_index"
}

Response 1:

{
  "schema": [
    {
      "name": "start_time",
      "type": "timestamp"
    }
  ],
  "datarows": [
    [
      "2022-12-01 02:46:18.406516529"
    ],
    [
      "2022-12-01 02:46:15.83386667"
    ],
...
],
    [
      "2022-12-01 02:57:49.658765135"
    ]
  ],
  "total": 200,
  "size": 200,
  "status": 200
}

200 seems to be the default size for a SQL query. However, when I try this query:

Query 2:

POST _plugins/_sql/
{
  "fetch_size" : 5,
  "query" : "SELECT start_time FROM my_opensearch_index"
}

Response 2:

{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "Index type [my_opensearch_index] does not exist",
    "type": "IllegalArgumentException"
  },
  "status": 500
}

Note that the only thing that changed in the Query 2 is the addition of "fetch_size" : 5, to the query.

NOTE: This was done on OpenSearch 2.5.3; I haven't tried this on ElasticSearch and I know they have a similar feature.

Answer 1

Try using index name in the FROM clause instead of an index alias.

Index aliases are not handled correctly in this case.