[BUG] fetch_size doesn't seem to work as described

[peter-iron]

Describe the bug
Hitting the /_opendistro/_sql endpoint with fetch_size in the body does not return a cursor field in the response even when it should. Providing a negative value does generate an error, so it is being provided and parsed properly and simply doesn't seem to have any impact on the return.

To Reproduce
Steps to reproduce the behavior:

1. Construct a basic query that returns more than 2 results in your local instance
2. using the application/json header: POST {"query": $query, "fetch_size": 2}
3. Observe that you see receive the full, normal (>2 docs) response without a cursor

Expected behavior
I would expect to receive only 2 docs in response with a cursor entry that I could use in a subsequent POST {"cursor": $cursor} call to obtain more results from the same query.

Plugins
Please list all plugins currently enabled.

Host/Environment (please complete the following information):

• OS: macOS
• Version 11.6

Additional context

• How it should work
• Limitations

[chloe-zh]

Hi @peter-iron thanks for reporting this issue, I tried to reproduce the error in my local cluster, but it is having proper behavior:

POST _plugins/_sql
{
  "fetch_size" : 5,
  "query": "SELECT Origin, Dest FROM opensearch_dashboards_sample_data_flights"
}

// result:
{
  "schema": [
    {
      "name": "Origin",
      "type": "keyword"
    },
    {
      "name": "Dest",
      "type": "keyword"
    }
  ],
  "cursor": "d:eyJhIjp7fSwicyI6IkZHbHVZMngxWkdWZlkyOXVkR1Y0ZEY5MWRXbGtEWEYxWlhKNVFXNWtSbVYwWTJnQkZuQXhVVU5KVjA1elVrdERkVmRNU2sweGVtNUhNM2NBQUFBQUFBQUFLeFpFVTJJeFpVaEplbEV4VjBOS2NsTjBZMEZvYlcxMyIsImMiOlt7Im5hbWUiOiJPcmlnaW4iLCJ0eXBlIjoia2V5d29yZCJ9LHsibmFtZSI6IkRlc3QiLCJ0eXBlIjoia2V5d29yZCJ9XSwiZiI6NSwiaSI6Im9wZW5zZWFyY2hfZGFzaGJvYXJkc19zYW1wbGVfZGF0YV9mbGlnaHRzIiwibCI6MTMwNTR9",
  "total": 13059,
  "datarows": [
    [
      "Frankfurt am Main Airport",
      "Sydney Kingsford Smith International Airport"
    ],
    [
      "Cape Town International Airport",
      "Venice Marco Polo Airport"
    ],
    [
      "Venice Marco Polo Airport",
      "Venice Marco Polo Airport"
    ],
    [
      "Naples International Airport",
      "Treviso-Sant'Angelo Airport"
    ],
    [
      "Licenciado Benito Juarez International Airport",
      "Xi'an Xianyang International Airport"
    ]
  ],
  "size": 5,
  "status": 200
}

Could you provide the version information of the OpenSearch/ODFE you are using?

[peter-iron]

I don't have admin access to the instance, so I'm inferring this:

• ODFE 1.13.2
• OpenSearch 1.0 because we are using OpenSearch on AWS, so I believe it is the only option per this page. The curl method for checking elasticsearch version does not seem to work for OpenSearch, but please let me know if there's another way to test.

[dai-chen]

Closing this since we're unable to reproduce. Feel free to reopen if more info you can provide. Thanks!