Date range query returns different results in ES7 vs. ES8 - does not respect include_lower

Elastic Stack Elasticsearch
1

Using a date range query with include_lower set to false is not working as expected in ES8. In ES7 a doc with a date of 1969-12-31 with date range query set to 1969-12-31 as the lower bound and include_lower=false would return no results, as expected. In ES8, the same query returns the doc. On the ES8 migration doc I don't see a change like this mentioned. Is this a bug? If so, should I report it to the github repo?

#create index
curl -X PUT localhost:9200/myidx

#create mapping
curl -H "Content-type: application/json" -X PUT localhost:9200/myidx/_mapping -d'{"dynamic":"strict","properties":{"id":{"type":"keyword"},"mydate":{"type":"date","format":"yyyy-MM-ddXXX"}}}'

#Add doc with value 1969-12-31
curl -H "Content-type: application/json" -X POST localhost:9200/myidx/_doc/1 -d'{"id":"1","mydate":"1969-12-31Z"}'

#Query for date range from 1969-12-31 to 2030-10-31 and EXCLUDE the lower and upper endpoints. EXPECT: no result since we are excluding 1969-12-31. The value "-86400000" is 1969-12-31
curl -H "Content-type: application/json" -X POST localhost:9200/myidx/_search -d'{"from":0,"size":5,"query":{"bool":{"must":[{"bool":{"filter":[{"bool":{"must":[{"range":{"mydate":{"from":-86400000,"to":null,"include_lower":false,"include_upper":true,"format":"epoch_millis","boost":1.0}}},{"range":{"mydate":{"from":null,"to":1919635200000,"include_lower":true,"include_upper":false,"format":"epoch_millis","boost":1.0}}}],"adjust_pure_negative":true,"boost":1.0}}],"adjust_pure_negative":true,"boost":1.0}}],"adjust_pure_negative":true,"boost":1.0}},"track_total_hits":2147483647}'

Using ES7 via docker run -e cluster.name=docker-cluster -e cluster.initial_master_nodes=myes -e node.name=myes docker.elastic.co/elasticsearch/elasticsearch:7.17.10 I get 0 results:

{"took":29,"timed_out":false,"_shards":{"total":1,"successful":1,"skipped":0,"failed":0},"hits":{"total":{"value":0,"relation":"eq"},"max_score":null,"hits":[]}}

Using ES8 via docker run -e xpack.security.enabled=false -e cluster.name=docker-cluster -e cluster.initial_master_nodes=myes -e node.name=myes docker.elastic.co/elasticsearch/elasticsearch:8.13.4 I get 1 result:

{"took":5,"timed_out":false,"_shards":{"total":1,"successful":1,"skipped":0,"failed":0},"hits":{"total":{"value":1,"relation":"eq"},"max_score":0.0,"hits":[{"_index":"myidx","_id":"1","_score":0.0,"_source":{"id":"1","mydate":"1969-12-31Z"}}]}}
2

The follow up investigation I did was whether all dates below 1970 (the linux epoch 0 time) were having this issue and the answer is yes.

So this time I add 1950-12-31

curl -X PUT localhost:9200/myidx
	
curl -H "Content-type: application/json" -X PUT localhost:9200/myidx/_mapping -d'{"dynamic":"strict","properties":{"id":{"type":"keyword"},"mydate":{"type":"date","format":"yyyy-MM-ddXXX"}}}'
	
curl -H "Content-type: application/json" -X POST localhost:9200/myidx/_doc/1 -d'{"id":"1","mydate":"1950-12-31Z"}'

In ES7

#1950 returns no results as expected
curl -H "Content-type: application/json" -X POST localhost:9200/myidx/_search -d'{"from":0,"size":5,"query":{"bool":{"must":[{"bool":{"filter":[{"bool":{"must":[{"range":{"mydate":{"from":-599702400000,"to":null,"include_lower":false,"include_upper":true,"format":"epoch_millis","boost":1.0}}},{"range":{"mydate":{"from":null,"to":1919635200000,"include_lower":true,"include_upper":false,"format":"epoch_millis","boost":1.0}}}],"adjust_pure_negative":true,"boost":1.0}}],"adjust_pure_negative":true,"boost":1.0}}],"adjust_pure_negative":true,"boost":1.0}},"track_total_hits":2147483647}'
		
{"took":3,"timed_out":false,"_shards":{"total":1,"successful":1,"skipped":0,"failed":0},"hits":{"total":{"value":0,"relation":"eq"},"max_score":null,"hits":[]}}
		
#1950 minus one second returns result as expected
curl -H "Content-type: application/json" -X POST localhost:9200/myidx/_search -d'{"from":0,"size":5,"query":{"bool":{"must":[{"bool":{"filter":[{"bool":{"must":[{"range":{"mydate":{"from":-599702401000,"to":null,"include_lower":false,"include_upper":true,"format":"epoch_millis","boost":1.0}}},{"range":{"mydate":{"from":null,"to":1919635200000,"include_lower":true,"include_upper":false,"format":"epoch_millis","boost":1.0}}}],"adjust_pure_negative":true,"boost":1.0}}],"adjust_pure_negative":true,"boost":1.0}}],"adjust_pure_negative":true,"boost":1.0}},"track_total_hits":2147483647}'
		
{"took":7,"timed_out":false,"_shards":{"total":1,"successful":1,"skipped":0,"failed":0},"hits":{"total":{"value":1,"relation":"eq"},"max_score":0.0,"hits":[{"_index":"myidx","_type":"_doc","_id":"1","_score":0.0,"_source":{"id":"1","mydate":"1950-12-31Z"}}]}}

In ES8:

#1950 should not return results, but does
curl -H "Content-type: application/json" -X POST localhost:9200/myidx/_search -d'{"from":0,"size":5,"query":{"bool":{"must":[{"bool":{"filter":[{"bool":{"must":[{"range":{"mydate":{"from":-599702400000,"to":null,"include_lower":false,"include_upper":true,"format":"epoch_millis","boost":1.0}}},{"range":{"mydate":{"from":null,"to":1919635200000,"include_lower":true,"include_upper":false,"format":"epoch_millis","boost":1.0}}}],"adjust_pure_negative":true,"boost":1.0}}],"adjust_pure_negative":true,"boost":1.0}}],"adjust_pure_negative":true,"boost":1.0}},"track_total_hits":2147483647}'
		
{"took":3,"timed_out":false,"_shards":{"total":1,"successful":1,"skipped":0,"failed":0},"hits":{"total":{"value":1,"relation":"eq"},"max_score":0.0,"hits":[{"_index":"myidx","_id":"1","_score":0.0,"_source":{"id":"1","mydate":"1950-12-31Z"}}]}}
		
#1950 plus one second does not return results
curl -H "Content-type: application/json" -X POST localhost:9200/myidx/_search -d'{"from":0,"size":5,"query":{"bool":{"must":[{"bool":{"filter":[{"bool":{"must":[{"range":{"mydate":{"from":-599702399000,"to":null,"include_lower":false,"include_upper":true,"format":"epoch_millis","boost":1.0}}},{"range":{"mydate":{"from":null,"to":1919635200000,"include_lower":true,"include_upper":false,"format":"epoch_millis","boost":1.0}}}],"adjust_pure_negative":true,"boost":1.0}}],"adjust_pure_negative":true,"boost":1.0}}],"adjust_pure_negative":true,"boost":1.0}},"track_total_hits":2147483647}'

{"took":2,"timed_out":false,"_shards":{"total":1,"successful":1,"skipped":0,"failed":0},"hits":{"total":{"value":0,"relation":"eq"},"max_score":null,"hits":[]}}
		
#What if we put both ends of the date range in the same filter? It still returned the data it was not supposed to
curl -H "Content-type: application/json" -X POST localhost:9200/myidx/_search -d'{"from":0,"size":5,"query":{"bool":{"must":[{"bool":{"filter":[{"bool":{"must":[{"range":{"mydate":{"from":-599702400000,"to":1919635200000,"include_lower":false,"include_upper":false,"format":"epoch_millis","boost":1.0}}}],"adjust_pure_negative":true,"boost":1.0}}],"adjust_pure_negative":true,"boost":1.0}}],"adjust_pure_negative":true,"boost":1.0}},"track_total_hits":2147483647}'
		
{"took":3,"timed_out":false,"_shards":{"total":1,"successful":1,"skipped":0,"failed":0},"hits":{"total":{"value":1,"relation":"eq"},"max_score":0.0,"hits":[{"_index":"myidx","_id":"1","_score":0.0,"_source":{"id":"1","mydate":"1950-12-31Z"}}]}}
3

Doing the same test with dates above 1970 work as expected!!!

4

I can reproduce your findings. I used a much simple example:

DELETE /myidx
PUT /myidx
{
  "mappings": {
    "properties": {
      "mydate": {
        "type": "date",
        "format": "yyyy-MM-ddXXX"
      }
    }
  }
}
PUT /myidx/_doc/1?refresh=true
{
  "mydate": "1969-12-31Z"
}
GET /myidx/_search?filter_path=hits.total.value
{
  "query": {
    "range": {
      "mydate": {
        "from": "1969-12-31Z",
        "include_lower": false
      }
    }
  }
}
# 1969-12-31Z as ms since epoch
GET /myidx/_search?filter_path=hits.total.value
{
  "query": {
    "range": {
      "mydate": {
        "from": -86400000,
        "include_lower": false
      }
    }
  }
}
# 1969-12-31Z as ms since epoch - 1ms
GET /myidx/_search?filter_path=hits.total.value
{
  "query": {
    "range": {
      "mydate": {
        "from": -86399999,
        "include_lower": true
      }
    }
  }
}

This gives:

# 1969-12-31Z
# GET /myidx/_search?filter_path=hits.total.value 200 OK
{
  "hits": {
    "total": {
      "value": 0
    }
  }
}
# -86400000
# GET /myidx/_search?filter_path=hits.total.value 200 OK
{
  "hits": {
    "total": {
      "value": 1
    }
  }
}
# -86399999
# GET /myidx/_search?filter_path=hits.total.value 200 OK
{
  "hits": {
    "total": {
      "value": 0
    }
  }
}

The second should have returned 0 hit indeed.
Let me ask internally if this is expected or indeed a regression.

5

7.17.21 indeed does respect the include_lower parameter:

DELETE /myidx
PUT /myidx
{
  "mappings": {
    "properties": {
      "mydate": {
        "type": "date",
        "format": "yyyy-MM-ddXXX"
      }
    }
  }
}
PUT /myidx/_doc/1?refresh=true
{
  "mydate": "1969-12-31Z"
}
GET /myidx/_search?filter_path=hits.total.value
{
  "query": {
    "range": {
      "mydate": {
        "from": "1969-12-31Z",
        "include_lower": false
      }
    }
  }
}
# 1969-12-31Z as ms since epoch
GET /myidx/_search?filter_path=hits.total.value
{
  "query": {
    "range": {
      "mydate": {
        "from": -86400000,
        "include_lower": false,
        "format": "epoch_millis"
      }
    }
  }
}

Gives:

# 1969-12-31Z
# GET /myidx/_search?filter_path=hits.total.value
{
  "hits" : {
    "total" : {
      "value" : 0
    }
  }
}
# -86400000
# GET /myidx/_search?filter_path=hits.total.value
{
  "hits" : {
    "total" : {
      "value" : 0
    }
  }
}

But, there's another problem

# 1969-12-31Z as ms since epoch - 1ms
GET /myidx/_search?filter_path=hits.total.value
{
  "query": {
    "range": {
      "mydate": {
        "from": -86399999,
        "include_lower": true,
        "format": "epoch_millis"
      }
    }
  }
}

is failing now:

{
  "error" : {
    "root_cause" : [
      {
        "type" : "parse_exception",
        "reason" : "failed to parse date field [-86399999] with format [epoch_millis]: [failed to parse date field [-86399999] with format [epoch_millis]]"
      }
    ],
    "type" : "search_phase_execution_exception",
    "reason" : "all shards failed",
    "phase" : "query",
    "grouped" : true,
    "failed_shards" : [
      {
        "shard" : 0,
        "index" : "myidx",
        "node" : "8LA98bEGSEmHO4X-T0MDsg",
        "reason" : {
          "type" : "parse_exception",
          "reason" : "failed to parse date field [-86399999] with format [epoch_millis]: [failed to parse date field [-86399999] with format [epoch_millis]]",
          "caused_by" : {
            "type" : "illegal_argument_exception",
            "reason" : "failed to parse date field [-86399999] with format [epoch_millis]",
            "caused_by" : {
              "type" : "date_time_parse_exception",
              "reason" : "Failed to parse with all enclosed parsers"
            }
          }
        }
      }
    ]
  },
  "status" : 400
}

I think we changed the implementation for dates and we are using now the JDK instead of JODA. May be that's the reason.