Why is my Opensearch query returning results in the wrong order?

Question

I'm using Opensearch to retrieve some documents from my index. Here is the query I'm using:

PUT /your_index
{
  "settings": {
    "number_of_shards": 3,
    "number_of_replicas": 1
  },
  "mappings": {
    "properties": {
      "ts_id": {
        "type": "long"
      },
      "myid": {
        "type": "keyword"
      }
    }
  }
}


PUT /your_index/_doc/3
{
  "myid": "1",
  "ts_id": 1703462528114626562
}

PUT /your_index/_doc/2
{
  "myid": "1",
  "ts_id": 1703462528114626560
}

PUT /your_index/_doc/1
{
  "myid": "1",
  "ts_id": 1703462528114626561
}

GET /your_index/_search

{
  "size": 3,
  "sort": [ { 
     "ts_id": {"order": "desc"}
  }],
  "query": {
    "bool": {
      "filter": [
        { "term": { "myid": "your_value" } }
      ]
    }
  }
}

result has wrong order:

eg:

{
  "took": 4,
  "timed_out": false,
  "_shards": {
    "total": 3,
    "successful": 3,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 9,
      "relation": "eq"
    },
    "max_score": null,
    "hits": [
      {... "ts_id": 1703462528114626562, "_shard":"202406[0]"},
      {... "ts_id": 1703462528114626560, "_shard":"202406[0]"},
      {... "ts_id": 1703462528114626561}, "_shard":"202406[1]",
    ]

Can anyone help me understand why this might be happening and how I can ensure the results are returned in the correct order?

---

Update: I have added additional information to the description, particularly specific numbers that were used for sorting. Previously, I had added smaller ones for simplicity. It turns out that their size is significant.

It turns out that the sorting problem is due to the large number in ts_id. In the documentation, it says long 2^63-1, but it looks like the problem might be related to JavaScript or JSON.

I added a query that replicates the problem. I am doing the query in the dev tools.

ts_id in the database looks correct (it is not truncated). I still do not understand whether the problem lies in the encoding of PUT or GET, or maybe inside the sorting engine.

Answer 1

Disclaimer: Not a proper answer. I have done my test on elasticsearch, I'll spin up an opensearch cluster later.

TLDR;

On elasticsearch 8.14.x the sorting works just fine. Although the Dev Tools display surprising values

Kibana's behaviours

This is the result I get from a search in Kibana's dev tools:

{
  "took": 1,
  "timed_out": false,
  "_shards": { "total": 1, "successful": 1, "skipped": 0, "failed": 0 },
  "hits": {
    "total": { "value": 3, "relation": "eq" },
    "max_score": null,
    "hits": [
      {
        "_index": "78587377",
        "_id": "2",
        "_score": null,
        "_source": { "ts_id": "1703462528114626562" },
        "fields": { "ts_id": [1703462528114626600] },
        "sort": [1703462528114626600]
      },
      {
        "_index": "78587377",
        "_id": "1",
        "_score": null,
        "_source": { "ts_id": "1703462528114626561" },
        "fields": { "ts_id": [1703462528114626600] },
        "sort": [1703462528114626600]
      },
      {
        "_index": "78587377",
        "_id": "0",
        "_score": null,
        "_source": { "ts_id": "1703462528114626560" },
        "fields": { "ts_id": [1703462528114626600] },
        "sort": [1703462528114626600]
      }
    ]
  }
}

fields and sort are containing a rounded value, while the _source is correct.

But I could narrow down this issue to JSON handling in kibana.

As when I perform the same query via a terminal I get:

{
  "took": 1,
  "timed_out": false,
  "_shards": { "total": 1, "successful": 1, "skipped": 0, "failed": 0 },
  "hits": {
    "total": { "value": 3, "relation": "eq" },
    "max_score": null,
    "hits": [
      {
        "_index": "78587377",
        "_id": "2",
        "_score": null,
        "_source": { "ts_id": "1703462528114626562" },
        "fields": { "ts_id": [1703462528114626562] },
        "sort": [1703462528114626562]
      },
      {
        "_index": "78587377",
        "_id": "1",
        "_score": null,
        "_source": { "ts_id": "1703462528114626561" },
        "fields": { "ts_id": [1703462528114626561] },
        "sort": [1703462528114626561]
      },
      {
        "_index": "78587377",
        "_id": "0",
        "_score": null,
        "_source": { "ts_id": "1703462528114626560" },
        "fields": { "ts_id": [1703462528114626560] },
        "sort": [1703462528114626560]
      }
    ]
  }
}

To Reproduce:

DELETE /78587377

PUT /78587377
{
  "mappings": {
    "properties": {
      "ts_id": {
        "type": "long"
      },
      "myid": {
        "type": "keyword"
      }
    }
  }
}

PUT 78587377/_bulk
{"index":{"_id":2}}
{"myid":"2","ts_id":"1703462528114626562"}
{"index":{"_id":0}}
{"myid":"0","ts_id":"1703462528114626560"}
{"index":{"_id":1}}
{"myid":"1","ts_id":"1703462528114626561"}

GET /78587377/_search
{
  "size": 3,
  "sort": [ { 
     "ts_id": {"order": "desc"}
  }],
  "_source": "ts_id",
  "fields": [
    "ts_id"
  ]
}