# Date range query returns different results in ES7 vs. ES8 - does not respect include\_lower

**URL:** <https://discuss.elastic.co/t/date-range-query-returns-different-results-in-es7-vs-es8-does-not-respect-include-lower/360844>\
**Category:** Elasticsearch\
**Tags:** docker\
**Created:** [June 5, 2024, 11:10am UTC](https://discuss.elastic.co/t/date-range-query-returns-different-results-in-es7-vs-es8-does-not-respect-include-lower/360844 "2024-06-05T11:10:06Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![buitcj](https://avatars.discourse-cdn.com/v4/letter/b/a587f6/32.png) [@buitcj](https://discuss.elastic.co/u/buitcj)\
**Post date:** [June 5, 2024, 11:10am UTC](https://discuss.elastic.co/t/date-range-query-returns-different-results-in-es7-vs-es8-does-not-respect-include-lower/360844/1 "2024-06-05T11:10:06Z")

</div>

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](https://www.elastic.co/guide/en/elasticsearch/reference/current/migrating-8.0.html) I don't see a change like this mentioned. Is this a bug? If so, should I report it to the github repo?

```auto
#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:

```auto
{"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:

```auto
{"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"}}]}}

```

---

<div class="post-metadata">

**Author:** ![buitcj](https://avatars.discourse-cdn.com/v4/letter/b/a587f6/32.png) [@buitcj](https://discuss.elastic.co/u/buitcj)\
**Post date:** [June 5, 2024, 11:17am UTC](https://discuss.elastic.co/t/date-range-query-returns-different-results-in-es7-vs-es8-does-not-respect-include-lower/360844/2 "2024-06-05T11:17:18Z")

</div>

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

```auto
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

```auto
#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:

```auto
#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"}}]}}

```

---

<div class="post-metadata">

**Author:** ![buitcj](https://avatars.discourse-cdn.com/v4/letter/b/a587f6/32.png) [@buitcj](https://discuss.elastic.co/u/buitcj)\
**Post date:** [June 5, 2024, 11:24am UTC](https://discuss.elastic.co/t/date-range-query-returns-different-results-in-es7-vs-es8-does-not-respect-include-lower/360844/3 "2024-06-05T11:24:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [June 6, 2024, 8:54am UTC](https://discuss.elastic.co/t/date-range-query-returns-different-results-in-es7-vs-es8-does-not-respect-include-lower/360844/4 "2024-06-06T08:54:25Z")

</div>

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

```auto
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:

```auto
# 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.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [June 6, 2024, 9:08am UTC](https://discuss.elastic.co/t/date-range-query-returns-different-results-in-es7-vs-es8-does-not-respect-include-lower/360844/5 "2024-06-06T09:08:26Z")

</div>

7.17.21 indeed does respect the `include_lower` parameter:

```auto
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:

```auto
# 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

```auto
# 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:

```auto
{
  "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.