SQL: Wrong sort order for derived long fields

[Luegg]

Given an index long_oder with the following content:

POST long_order/_bulk

{"index": {"_id": 1}}
{"l": 9223372036854775807}
{"index": {"_id": 2}}
{"l": 9223372036854775805}
{"index": {"_id": 3}}
{"l": 9223372036854775806}
{"index": {"_id": 4}}
{"l": 9223372036854775804}

The query SELECT l - 1 + 1 FROM long_order ORDER BY 1 retrieves the records in the wrong order:

     l - 1 + 1     
-------------------
9223372036854775807
9223372036854775805
9223372036854775806
9223372036854775804

This bug is likely caused by the precision loss of converting long values to double in the script sort context:

{"size":1000,"_source":false,"fields":[{"field":"l"}],"sort":[{"_script":{"script":{"source":"InternalQlScriptUtils.nullSafeSortNumeric(InternalSqlScriptUtils.add(InternalSqlScriptUtils.sub(InternalQlScriptUtils.docValue(doc,params.v0),params.v1),params.v2))","lang":"painless","params":{"v0":"l","v1":1,"v2":1}},"type":"number","order":"asc"}}]}

[elasticmachine]

Pinging @elastic/es-ql (Team:QL)

[chenyuzhi459]

Hello, it is really precision loss in InternalQlScriptUtils.nullSafeSortNumeric method, I have fixed it in #77701 , you can try it out.

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)