[BUG] when use "epoch_millis" format for date, got error when sorting in descending order

[erzhuerzhu]

Describe the bug
get error:
{"type":"response_handler_failure_transport_exception","reason":"java.time.DateTimeException: Field EpochMillis cannot be printed as the value -9223372036854775808 cannot be negative according to the SignStyle","caused_by":{"type":"date_time_exception","reason":"Field EpochMillis cannot be printed as the value -9223372036854775808 cannot be negative according to the SignStyle"}}}]},"status":500}

To Reproduce

1. create local and single opensearch server

docker pull opensearchproject/opensearch:2.7.0
docker run -d -p 9200:9200 -p 9600:9600 -e "discovery.type=single-node" opensearchproject/opensearch:2.7.0

2. create index with below setting

curl -ku 'admin:admin' -XPUT  'https://localhost:9200/test' -d '{"mappings":{"dynamic":"strict","properties":{"date1":{"type":"date","format":"epoch_millis"},"date2":{"type":"date","format":"yyyy-MM-dd"},"id":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":255}}}}}}' -H 'Content-Type: application/json'

{
    "date1": {
        "type": "date",
        "format": "epoch_millis"
    },
    "date2": {
        "type": "date",
        "format": "yyyy-MM-dd"
    },
    "id": {
        "type": "text",
        "fields": {
            "keyword": {
                "type": "keyword",
                "ignore_above": 255
            }
        }
    }
}

3. ingest data:

around 2000 documents, only 10 of date1 have value. all the other value of date1 are null
{
    'id': 2000,
    'date1': 1578712393000,
    'date2': '1979-01-11'
}

{
    'id': 2000,
    'date1': null,
    'date2': '1979-01-11'
}

4. use below api to query

{
    "query": {
        "bool": {
            "must": {
                "exists": {
                    "field": "id"
                }
            }
        }
    },
    "highlight": {
        "fields": {}
    },
    "size": 100,
    "from": 0,
    "sort": [{
        "date1": {
            "missing": "_last",
            "order": "asc"
        }
    }, {
        "id.keyword": "desc"
    }]
}

no issue

when change the order into desc, get below error:

{
    "query": {
        "bool": {
            "must": {
                "exists": {
                    "field": "id"
                }
            }
        }
    },
    "highlight": {
        "fields": {}
    },
    "size": 100,
    "from": 0,
    "sort": [{
        "date1": {
            "missing": "_last",
            "order": "desc"
        }
    }, {
        "id.keyword": "desc"
    }]
}

{
    "error": {
        "root_cause": [{
            "type": "response_handler_failure_transport_exception",
            "reason": "java.time.DateTimeException: Field EpochMillis cannot be printed as the value -9223372036854775808 cannot be negative according to the SignStyle"
        }],
        "type": "search_phase_execution_exception",
        "reason": "all shards failed",
        "phase": "query",
        "grouped": true,
        "failed_shards": [{
            "shard": 0,
            "index": "sort1",
            "node": "YUWWdMlhSX2RvXLCyLRWuw",
            "reason": {
                "type": "response_handler_failure_transport_exception",
                "reason": "java.time.DateTimeException: Field EpochMillis cannot be printed as the value -9223372036854775808 cannot be negative according to the SignStyle",
                "caused_by": {
                    "type": "date_time_exception",
                    "reason": "Field EpochMillis cannot be printed as the value -9223372036854775808 cannot be negative according to the SignStyle"
                }
            }
        }]
    },
    "status": 500
}

Expected behavior

Plugins
no

Screenshots

Host/Environment (please complete the following information):

• OS: docker on mac
• Version :2.7.0

Additional context
releated issue :elastic/elasticsearch#52396 (comment)
if use yyyy-MM-dd format for field date1, I will get long overflow error for asc order for date1, so I changed it into epoch_millis format

tested in 2.10.0， still get same error

[msfroh]

This sounds like it's "just" a problem with the formatter. Instead of trying to format the epoch timestamp, we should detect the sentinel missing value (in this case, Long.MIN_VALUE), and format it differently -- either by excluding the field (since it's missing) or maybe we output it as "missing", or something.

[afn7081]

can i take this up if not already taken?

[dblock]

@afn7081 please do!

[msfroh]

@afn7081 -- Are you still planning to work on this? Thanks!

[Slawutich]

Hello @afn7081

I also have this problem, is there any solution?

[msfroh]

I've been digging into this a bit. I believe the underlying problem is an overflow (underflow?) in this expression:

OpenSearch/server/src/main/java/org/opensearch/common/time/EpochTime.java

Line 129 in a17692d

long instantSecondsInMillis = temporal.getLong(ChronoField.INSTANT_SECONDS) * 1_000;

[msfroh]

I managed to fix it by replacing that line with:

            long instantSeconds = temporal.getLong(ChronoField.INSTANT_SECONDS);
            if (instantSeconds < Long.MIN_VALUE / 1000L || instantSeconds > Long.MAX_VALUE / 1000L) {
                // Multiplying would yield integer overflow
                return Long.MAX_VALUE;
            }
            long instantSecondsInMillis = instantSeconds * 1_000;