[BUG] no such index[]: Pagination fails for many columns

[andreaskulicke]

What is the bug?
In case many columns (document properties) exist pagination fails for SQL queries.

How can one reproduce the bug?
Steps to reproduce the behavior:

1. Create an empty index

PUT /test

2. Add two documents with many columns (could be the same request twice)

POST /test/_doc?refresh=true
{
"column1": "value",
"column2": "value",
"column3": "value",
"column4": "value",
"column5": "value",
"column6": "value",
"column7": "value",
"column8": "value",
"column9": "value",
"column10": "value",
"column11": "value",
"column12": "value",
"column13": "value",
"column14": "value",
"column15": "value",
"column16": "value",
"column17": "value",
"column18": "value",
"column19": "value",
"column20": "value",
"column21": "value",
"column22": "value",
"column23": "value",
"column24": "value",
"column25": "value",
"column26": "value",
"column27": "value",
"column28": "value",
"column29": "value",
"column30": "value",
"column31": "value",
"column32": "value",
"column33": "value",
"column34": "value",
"column35": "value",
"column36": "value",
"column37": "value",
"column38": "value",
"column39": "value",
"column40": "value",
"column41": "value",
"column42": "value",
"column43": "value",
"column44": "value",
"column45": "value",
"column46": "value",
"column47": "value",
"column48": "value",
"column49": "value",
"column50": "value",
"column51": "value",
"column52": "value",
"column53": "value",
"column54": "value",
"column55": "value",
"column56": "value",
"column57": "value",
"column58": "value",
"column59": "value",
"column60": "value",
"column61": "value",
"column62": "value",
"column63": "value",
"column64": "value",
"column65": "value",
"column66": "value",
"column67": "value",
"column68": "value",
"column69": "value",
"column70": "value",
"column71": "value",
"column72": "value",
"column73": "value",
"column74": "value",
"column75": "value",
"column76": "value",
"column77": "value",
"column78": "value",
"column79": "value",
"column80": "value",
"column81": "value",
"column82": "value",
"column83": "value",
"column84": "value",
"column85": "value",
"column86": "value",
"column87": "value",
"column88": "value",
"column89": "value",
"column90": "value",
"column91": "value",
"column92": "value",
"column93": "value",
"column94": "value",
"column95": "value",
"column96": "value",
"column97": "value",
"column98": "value",
"column99": "value",
"column100": "value",
"column101": "value",
"column102": "value",
"column103": "value",
"column104": "value",
"column105": "value",
"column106": "value",
"column107": "value",
"column108": "value",
"column109": "value",
"column110": "value",
"column111": "value",
"column112": "value",
"column113": "value",
"column114": "value",
"column115": "value",
"column116": "value",
"column117": "value",
"column118": "value",
"column119": "value",
"column120": "value",
"column121": "value",
"column122": "value",
"column123": "value",
"column124": "value",
"column125": "value",
"column126": "value",
"column127": "value",
"column128": "value",
"column129": "value",
"column130": "value",
"column131": "value",
"column132": "value",
"column133": "value",
"column134": "value",
"column135": "value",
"column136": "value",
"column137": "value",
"column138": "value",
"column139": "value",
"column140": "value",
"column141": "value",
"column142": "value",
"column143": "value",
"column144": "value",
"column145": "value",
"column146": "value",
"column147": "value",
"column148": "value",
"column149": "value",
"column150": "value"
}

3. Send simple SQL query for all columns

POST /_plugins/_sql
{
"fetch_size" : 1,
"query" : "SELECT * FROM test"
}

4. Copy returned "cursor" value for next pagination request

POST /_plugins/_sql
{
"cursor" : "n:..."
}

Response:

{
"error": {
"reason": "Error occurred in OpenSearch engine: no such index []",
"details": "[] IndexNotFoundException[no such index []]\nFor more details, please send request for Json format to see the raw response from OpenSearch engine.",
"type": "IndexNotFoundException"
},
"status": 404
}

What is the expected behavior?
The pagination requests return the next data.

What is your host/environment?
Docker with opensearch version 2.11.0.0.

Do you have any additional context?
Deserialization of the cursor fails in OpenSearchIndexScan.java:readExternal().

    byte[] requestStream = new byte[reqSize];
    in.read(requestStream);

In this case the bytes of the OpenSearchScrollRequest to read are more that the internal 1024 bytes buffer of the ObjectInput stream. So not all bytes are read. As the read() returns the number of read bytes it should be done in a loop like, e.g.:

    byte[] requestStream = new byte[reqSize];
    int read = 0;
    do {
      int currentRead = in.read(requestStream, read, reqSize - read);
      if (currentRead == -1) {
        break;
      }
      read += currentRead;
    } while (read < reqSize);

I guess there is also some unnecessary writing of bytes in writeExternal():

    // 2. Extract byte[] from the opensearch byte stream
    var reqAsBytes = reqOut.bytes().toBytesRef().bytes;

    // 3. Write out the byte[] to object output stream.
    out.writeInt(reqAsBytes.length);
    out.write(reqAsBytes);

This writes the complete buffer, not just the serialized bytes.
So it should be something like:

    // 2. Extract byte[] from the opensearch byte stream
    var reqAsBytes = reqOut.bytes().toBytesRef().bytes;

    // 3. Write out the byte[] to object output stream.
    out.writeInt(reqOut.size());
    out.write(reqAsBytes, 0, reqOut.size());

I'll try to put this together as a pull request.