[BUG] "arithmetic_exception: long overflow" while running sort query on date field

[NamrataNerli]

Describe the bug

We are getting "arithmetic_exception: long overflow" intermittently when we sort the results on date field.
This is the query we are using:

{
   "query":{
      "bool":{
         "adjust_pure_negative":true,
         "must":[
            {
               "match":{
                  "id":"7273617yygs2t7812ygsy"
               }
            },
            {
               "match":{
                  "scope":"2020"
               }
            },
            {
               "bool":{
                  "adjust_pure_negative":true,
                  "must_not":[
                     {
                        "match":{
                           "status":"COMPLETED"
                        }
                     }
                  ],
                  "boost":1
               }
            }
         ],
         "boost":1
      }
   },
   "_source":{
      "excludes":[
         
      ],
      "includes":[
         "dueDate",
         "scope",
         "id",
         "status"
      ]
   },
   "sort":[
      {
         "dueDate":{
            "order":"asc"
         }
      }
   ]
}

This is error message :

Related component

Search

To Reproduce

We are using this date format on dueDate field.

This is query

{
   "query":{
      "bool":{
         "adjust_pure_negative":true,
         "must":[
            {
               "match":{
                  "id": "7273617yygs2t7812ygsy"
               }
            },
            {
               "match":{
                  "scope": "2020"
               }
            },
            {
               "bool":{
                  "adjust_pure_negative":true,
                  "must_not":[
                     {
                        "match":{
                           "status": "COMPLETED"
                        }
                     }
                  ],
                  "boost":1
               }
            }
         ],
         "boost":1
      }
   },
   "_source":{
      "excludes":[
         
      ],
      "includes":[
         "dueDate",
         "scope",
         "id",
         "status"
      ]
   },
   "sort":[
      {
         "dueDate":{
            "order":"asc"
         }
      }
   ]
}

Note: Some of the documents/records that are inserted on the index do have value for dueDate field and some of them do.

We tried changing the date format to this yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis||strict_date_optional_time, but still we were getting arithmetic_exception.

Expected behavior

We want to sort on date field without having any issues.

Additional Details

Plugins
Please list all plugins currently enabled.

Screenshots
If applicable, add screenshots to help explain your problem.

Host/Environment (please complete the following information):

• OS: [e.g. iOS]
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

[prudhvigodithi]

[Triage]
Hey @NamrataNerli I was trying to re-produce the error but couldn't, please check the following sample and let me know if I'm missing anything, I was able to get the output without the arithmetic exception.

Can you please share some sample data/commands to re-produce this ?

curl -X PUT "localhost:9200/test_index" -H 'Content-Type: application/json' -d'
{
  "mappings": {
    "properties": {
      "id": {
        "type": "keyword"
      },
      "scope": {
        "type": "keyword"
      },
      "status": {
        "type": "keyword"
      },
      "dueDate": {
        "type": "date",
        "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis||strict_date_optional_time"
      }
    }
  }
}'


curl -X POST "localhost:9200/test_index/_doc/1" -H 'Content-Type: application/json' -d'
{
  "id": "7273617yygs2t7812ygsy",
  "scope": "2020",
  "status": "IN_PROGRESS",
  "dueDate": "2024-11-22 13:00:00"
}'

curl -X POST "localhost:9200/test_index/_doc/2" -H 'Content-Type: application/json' -d'
{
  "id": "7273617yygs2t7812ygsy",
  "scope": "2020",
  "status": "IN_PROGRESS"
}'


curl -X POST "localhost:9200/test_index/_doc/3" -H 'Content-Type: application/json' -d'
{
  "id": "7273617yygs2t7812ygsy",
  "scope": "2020",
  "status": "IN_PROGRESS",
  "dueDate": "2024-11-23"
}'


curl -X POST "localhost:9200/test_index/_doc/4" -H 'Content-Type: application/json' -d'
{
  "id": "7273617yygs2t7812ygsy",
  "scope": "2020",
  "status": "IN_PROGRESS",
  "dueDate": null
}'

curl -X POST "localhost:9200/test_index/_doc/5" -H 'Content-Type: application/json' -d'
{
  "id": "7273617yygs2t7812ygsy",
  "scope": "2020",
  "status": "IN_PROGRESS"
}'

curl -X GET "localhost:9200/test_index/_search" -H 'Content-Type: application/json' -d'
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "id": "7273617yygs2t7812ygsy"
          }
        },
        {
          "match": {
            "scope": "2020"
          }
        },
        {
          "bool": {
            "must_not": [
              {
                "match": {
                  "status": "COMPLETED"
                }
              }
            ]
          }
        }
      ]
    }
  },
  "_source": {
    "includes": [
      "dueDate",
      "scope",
      "id",
      "status"
    ]
  },
  "sort": [
    {
      "dueDate": {
        "order": "asc"
      }
    }
  ]
}'

Output

{
 "took": 4,
 "timed_out": false,
 "_shards": {
   "total": 1,
   "successful": 1,
   "skipped": 0,
   "failed": 0
 },
 "hits": {
   "total": {
     "value": 5,
     "relation": "eq"
   },
   "max_score": null,
   "hits": [
     {
       "_index": "test_index",
       "_id": "1",
       "_score": null,
       "_source": {
         "scope": "2020",
         "dueDate": "2024-11-22 13:00:00",
         "id": "7273617yygs2t7812ygsy",
         "status": "IN_PROGRESS"
       },
       "sort": [
         1732280400000
       ]
     },
     {
       "_index": "test_index",
       "_id": "3",
       "_score": null,
       "_source": {
         "scope": "2020",
         "dueDate": "2024-11-23",
         "id": "7273617yygs2t7812ygsy",
         "status": "IN_PROGRESS"
       },
       "sort": [
         1732320000000
       ]
     },
     {
       "_index": "test_index",
       "_id": "2",
       "_score": null,
       "_source": {
         "scope": "2020",
         "id": "7273617yygs2t7812ygsy",
         "status": "IN_PROGRESS"
       },
       "sort": [
         9223372036854775807
       ]
     },
     {
       "_index": "test_index",
       "_id": "4",
       "_score": null,
       "_source": {
         "scope": "2020",
         "dueDate": null,
         "id": "7273617yygs2t7812ygsy",
         "status": "IN_PROGRESS"
       },
       "sort": [
         9223372036854775807
       ]
     },
     {
       "_index": "test_index",
       "_id": "5",
       "_score": null,
       "_source": {
         "scope": "2020",
         "id": "7273617yygs2t7812ygsy",
         "status": "IN_PROGRESS"
       },
       "sort": [
         9223372036854775807
       ]
     }
   ]
 }
}

Thank you
@msfroh @getsaurabh02

[NamrataNerli]

Hi @prudhvigodithi ,
We are seeing this issue intermittently on large dataset. On our cluster we have 14 indices & they have same mapping which is this

{
  "mappings": {
    "properties": {
      "id": {
        "type": "keyword"
      },
      "scope": {
        "type": "keyword"
      },
      "status": {
        "type": "keyword"
      },
      "dueDate": {
        "type": "date",
        "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis||date_optional_time"
      }
    }
  }
}

When we execute the query against all these 14 indices we are getting arithmetic exceptions . This is how we are executing the query.

GET test-index-*/_search
{
   "query":{
      "bool":{
         "adjust_pure_negative":true,
         "must":[
            {
               "match":{
                  "id": "7273617yygs2t7812ygsy"
               }
            },
            {
               "match":{
                  "scope": "2020"
               }
            },
            {
               "bool":{
                  "adjust_pure_negative":true,
                  "must_not":[
                     {
                        "match":{
                           "status": "COMPLETED"
                        }
                     }
                  ],
                  "boost":1
               }
            }
         ],
         "boost":1
      }
   },
   "_source":{
      "excludes":[
         
      ],
      "includes":[
         "dueDate",
         "scope",
         "id",
         "status"
      ]
   },
   "sort":[
      {
         "dueDate":{
            "order":"asc"
         }
      }
   ]
}

This is the data distribution on the indices

When we tried to execute the same query against smaller dataset(i.e. against 10% of the above mentioned data volume) , we did not face any issues.

We are currently using OS version 2.11 & these are sample document

Document with dueDate value

{
    "scope": "2020",
    "status": "COMPLETED",
    "dueDate" : "2024-01-27T13:12:50.980",
    "id" :  "7273617yygs2t7812ygsy"
}

Document without dueDate value

{
    "scope": "2020",
    "status": "COMPLETED",
    "id" :  "7273617yygs2t7812ygsy"
}

[msfroh]

We are currently using OS version 2.11 & these are sample document

This may have been fixed by #12676, which was merged for OpenSearch 2.13.

Are you able to try upgrading to 2.13 or later and testing?