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

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 : Screenshot 2024-11-22 at 1 05 49 PM

Related component

Search

To Reproduce

We are using this date format on dueDate field. Screenshot 2024-11-22 at 1 26 43 PM

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.

About this issue

  • Original URL
  • State: closed
  • Created 2 years ago
  • Reactions: 1
  • Comments: 16 (11 by maintainers)

Most upvoted comments

@reta

@msfroh tagging you here since you’ve been looking into similar (or same?) issue before, may be missing some edge cases? Thanks @prudhvigodithi for looking into it.

Hello, everyone. @prudhvigodithi, I have tested again, using the same conditions shared for reproducing the error. Tried again with up to 20,000 documents with missing values for dueDate. From my end, the Arithmetic Exception was not triggered in any of the tests

Hello, @prudhvigodithi. I have cloned the fork, run the cluster, and tried to trigger the Arithmetic Exception. The exception was not triggered in any of the tests I ran. I tested with the provided conditions, ingesting increasing amounts of documents with missing dueDate values, up to 20,000 documents.

After multiple tries finally I was able to reproduce the error, I have created a small error_hunter.sh bash script for other to re-produce this arithmetic_exception. Added some comments to the script to understand the flow.

#!/bin/bash

OS_URL="http://localhost:9200"
INDEX_NAME="my-index"
BULK_FILE="bulk_data.json"
SHARD_COUNT=6

# 1. Delete existing index if it exists
echo "Deleting existing index..."
curl -X DELETE "$OS_URL/$INDEX_NAME"
sleep 2

# 2. Create index with multiple shards
echo "Creating index with $SHARD_COUNT shards..."
curl -X PUT "$OS_URL/$INDEX_NAME" -H "Content-Type: application/json" -d '{
  "settings": {
    "number_of_shards": '$SHARD_COUNT',
    "index.max_result_window": 50000
  },
  "mappings": {
    "dynamic": "strict",
    "properties": {
      "domainAttributes": {
        "properties": {
          "dueDate": {
            "type": "date",
            "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis||date_optional_time"
          },
          "firmId": {
            "type": "keyword"
          }
        }
      }
    }
  }
}'

sleep 2

# 3. TEst Data
echo "Generating distributed test data..."
> "$BULK_FILE"

# First add the records without dates (3500 docs)
for i in $(seq 1 3500); do
    routing_key=$((RANDOM % SHARD_COUNT))  # Distribute across shards
    echo '{"index":{"_index":"'$INDEX_NAME'","routing":"'$routing_key'"}}' >> "$BULK_FILE"
    echo '{"domainAttributes":{"firmId":"12345678910111213"}}' >> "$BULK_FILE"
done

# Add records with dates
for i in $(seq 1 50); do
    routing_key=$((RANDOM % SHARD_COUNT))
    date="2022-03-$((i % 30 + 1))T15:40:58.324"
    echo '{"index":{"_index":"'$INDEX_NAME'","routing":"'$routing_key'"}}' >> "$BULK_FILE"
    echo '{"domainAttributes":{"firmId":"12345678910111213","dueDate":"'$date'"}}' >> "$BULK_FILE"
done

# 4. Bulk Index the data
echo "Indexing data..."
curl -X POST "$OS_URL/_bulk" -H "Content-Type: application/json" --data-binary @"$BULK_FILE"

sleep 5

# 5. Function to perform search tests
run_search_test() {
    echo "Running search iteration $1..."
    response=$(curl -s -X GET "$OS_URL/$INDEX_NAME/_search?allow_partial_search_results=false&error_trace=true" -H "Content-Type: application/json" -d '{
      "query": {
        "match": {
          "domainAttributes.firmId": "12345678910111213"
        }
      },
      "sort": [
        {
          "domainAttributes.dueDate": {
            "order": "asc"
          }
        }
      ]
    }')
    
    if echo "$response" | grep -q "arithmetic_exception"; then
        echo "Found arithmetic exception!"
        echo "$response" | grep -A 2 "arithmetic_exception"
        return 0
    fi
    return 1
}

# 6. Run test iterations (running the query 1000 times)
echo "Running search tests..."
found_error=false
for i in $(seq 1 1000); do
    if run_search_test $i; then
        found_error=true
        break
    fi
    sleep 0.1
done

if [ "$found_error" = false ]; then
    echo "No error found after 100 attempts"
fi

# Clean up
rm "$BULK_FILE"
echo "Test completed."

I can see this error across the 2.x line (2.18, 2.17, 2.15, 2,13) but not on main branch. I will debug further to find the root cause and will create a PR.

Thank you.

Hello, everyone.

I replicated the issue by creating an index with different combinations of date formats for the dueDate field.

In my environment, the error was triggered when custom date formats were used (i.e., yyyy-MM-dd HH:mm:ss, yyyy-MM-dd), even when using each one of them alone.

When using “strict_date_optional_time” alone or paired with “epoch_millis” the issue did not show up.

# Reproduce the error ----------------------------------------

PUT my-index
{
  "mappings": {
    "dynamic": "strict",
    "properties": {
      "domainAttributes": {
        "properties": {
          "dueDate": {
            "type": "date",
            "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis||date_optional_time"
          },
          "firmId": {
            "type": "keyword"
          }
        }
      }
    }
  }
}

# Ingested from 30 to 50 records without dueDate in order to reproduce the error.

POST _bulk
{"index":{"_index":"my-index"}}
{"domainAttributes":{"firmId":"12345678910111213"}}


# The issue is intermittent. Some searches are completely successful and others are not

GET my-index/_search
{
  "query" :
  {
    "match": {
      "domainAttributes.firmId": "12345678910111213"
      }
  },
  "sort":[
      {
         "domainAttributes.dueDate":{
            "order":"asc"
         }
      }
   ]
}


# Avoid the error ----------------------------------------------

# Possible workaround 1
# It succeeded every time it was tested. Tested with 3,500 records without dueDate, and 1 with a dueDate value (i.e., 2022-03-31T15:40:58.324)

GET my-index/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "domainAttributes.firmId": "12345678910111213"
          }
        },
        {
          "exists": {
            "field": "domainAttributes.dueDate"
          }
        }
      ]
    }
  },
  "sort": [
    {
      "domainAttributes.dueDate": {
        "order": "asc",
        "missing": "_last"
      }
    }
  ]
}


# Possible workaround 2
# Having only strict_date_optional_time seems to avoid the error (Tested with 200 records)
# Tested further with a common date (i.e., 2022-03-31T15:40:58.324) having 100 records with dueDate, and up to 3,000 records without dueDate. Searches succeeded every time.

PUT my-index
{
  "mappings": {
    "dynamic": "strict",
    "properties": {
      "domainAttributes": {
        "properties": {
          "dueDate": {
            "type": "date",
            "format": "strict_date_optional_time"
          },
          "firmId": {
            "type": "keyword"
          }
        }
      }
    }
  }
}