[BUG] Sorting not working well on scaled_float fields when using query

[AhmedEnnaime]

Description

I have this field in my opensearch index

"amount": {
  "type": "scaled_float",
  "scaling_factor": 10000
},

when i try to run a query to get my documents sorted by the amount the documents are not well sorted

GET myIndex/_search
{
  "_source": ["amount"],
  "query": {
    "match_all": {}
  },
  "sort": [
    {
      "amount": {
        "order": "desc"
      }
    }
  ],
  "from": 11,
  "size": 21
}

I saw an opened bug that kinda talks about the same issue if there's any news about it can you please let me know about it

Related similar bug: #12433

Related component

Search

To Reproduce

Create an index with a scaled_float field and add some large values and try to execute a normal search query that orders by that field and see if the results are well sorted or not. Please try to add a lot of values to reproduce it.

Expected behavior

Normally the documents should be sorted well either in a descending or ascending way.

Additional Details

Host/Environment :

• OS: [Linux Ubuntu 24.04]
• OpenSearch version: 2.19.1

[AhmedEnnaime]

I tried to execute the query in two different ways one using query and the other without the latter did succeed and sorted the documents well using the amount but the first one didn't if someone can explain why this behavior happens (Note: all of the documents have the same source_system)

Successful sorting:

GET myIndex/_search
{
  "_source": {
    "includes": [
      "country",
      "creation_timestamp",
      "amount"
    ]
  },
  "from": 0,
  "size": 10,
  "sort": [
    {
      "amount": {
        "order": "desc"
      }
    },
    {
      "@timestamp": {
        "order": "desc"
      }
    }
  ],
  "track_total_hits": true
}

Wrong sorting:

GET myIndex/_search
{
  "_source": {
    "includes": [
      "country",
      "creation_timestamp",
      "amount"
    ]
  },
  "from": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "match": {
            "source_system": {
              "query": "SAC"
            }
          }
        }
      ]
    }
  },
  "size": 10,
  "sort": [
    {
      "amount": {
        "order": "desc"
      }
    },
    {
      "@timestamp": {
        "order": "desc"
      }
    }
  ],
  "track_total_hits": true
}

[sandeshkr419]

[Search Triage] @expani - Do you mind taking a look once?

[AhmedEnnaime]

I was trying to figure out why the query with filter match is not sorting well that's when i tried to add "track_scores": true and it suddenly worked it sorted successfully i know that setting "track_scores": true starts compute and calculates scores but i'm wondering and i'm trying to find an answer why the query with the match filter without track scores is not sorting well knowing that if i modified the type of the amount to double instead of scaled_float (i don't wanna use this solution since it's gonna force me to re-ingest all my data) the query with filter match sorts normally without any issues.

GET myIndex/_search
{
  "_source": {
    "includes": [
      "country",
      "creation_timestamp",
      "amount"
    ]
  },
  "from": 0,
  "size": 10,
  "sort": [
    {
      "amount": {
        "order": "desc"
      }
    },
    {
      "@timestamp": {
        "order": "desc"
      }
    }
  ],
  "query": {
    "bool": {
      "filter": [
        {
          "match": {
            "source_system": {
              "query": "SAC"
            }
          }
        }
      ]
    }
  },
  "track_total_hits": true,
  "track_scores": true
}

[kkewwei]

@AhmedEnnaime The type like float, double, and scaled_float are prone to precision loss issues, which might be the root cause of the problem you facing. I'm trying to use BigDecimal as much as possible to avoid it.

Similar issue #14966 #12433

[AhmedEnnaime]

@kkewwei but what i'm wondering about why did it work when i removed this part from the query

"query": {
    "bool": {
      "filter": [
        {
          "match": {
            "source_system": {
              "query": "SAC"
            }
          }
        }
      ]
    }
  }

or when i add "track_scores": true

[kkewwei]

The type of source_system is scaled_float , so sorting by the field may produce unexpected results.

[AhmedEnnaime]

@kkewwei source_system is not scaled_float it's a keyword which i'm filtering with i'm sorting by the amount which is a scaled_fload, all my documents have the same value of source_system for now but i might have additional docs with different source_system values that's why i'm including this filter but when i include this filter query match the sorting goes wrong i remove it it works both queries returns the same result and docs the issue is just in sorting. When i add "track_scores": true while having that filter match query for source_system the sorting works well in this case as well.