Parser exception when use "include_upper":true in date range query

[silvina]

Hi,
I get parse_exception when I try to do this query:

{                                                      
   "from":0,
   "size":999,
   "query":{
      "bool":{
         "filter":[
            {
               "range":{
                  "date_created":{
                     "from":"2020-09-01T23:59:59.000-03:00",
                     "to":"2020-09-01T23:59:59.000-03:00",
                     "include_lower":true,
                     "include_upper":true, 
                     "format":"8uuuu-MM-dd'\''T'\''HH:mm:ss.SSSX||uuuu-MM-dd'\''T'\''HH:mm:ss.SSSXXX||uuuu-MM-dd'\''T'\''HH:mm:ss.SSSXXXXX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSXXX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSXXXXX",
                     "boost":1.0
                  }
               }
            }
         ],
         "adjust_pure_negative":true,
         "boost":1.0
      }
   },
   "sort":[
      {
         "date_created":{
            "order":"desc"
         }
      }
   ]
} 

Exception details:
{"error":{"root_cause":[{"type":"parse_exception","reason":"failed to parse date field [2020-09-01T23:59:59.000-03:00] with format [8uuuu-MM-dd'T'HH:mm:ss.SSSX||uuuu-MM-dd'T'HH:mm:ss.SSSXXX||uuuu-MM-dd'T'HH:mm:ss.SSSXXXXX||uuuu-MM-d'T'HH:mm:ss.SSSX||uuuu-MM-d'T'HH:mm:ss.SSSXXX||uuuu-MM-d'T'HH:mm:ss.SSSXXXXX]: [Text '2020-09-01T23:59:59.000-03:00' could not be parsed, unparsed text found at index 26]"}],"type":"search_phase_execution_exception","reason":"all shards failed","phase":"query","grouped":true,"failed_shards":[{"shard":0,"index":"users","node":"2IUwcGlGQ0mEzsQZ7NgGAg","reason":{"type":"parse_exception","reason":"failed to parse date field [2020-09-01T23:59:59.000-03:00] with format [8uuuu-MM-dd'T'HH:mm:ss.SSSX||uuuu-MM-dd'T'HH:mm:ss.SSSXXX||uuuu-MM-dd'T'HH:mm:ss.SSSXXXXX||uuuu-MM-d'T'HH:mm:ss.SSSX||uuuu-MM-d'T'HH:mm:ss.SSSXXX||uuuu-MM-d'T'HH:mm:ss.SSSXXXXX]: [Text '2020-09-01T23:59:59.000-03:00' could not be parsed, unparsed text found at index 26]","caused_by":{"type":"date_time_parse_exception","reason":"Text '2020-09-01T23:59:59.000-03:00' could not be parsed, unparsed text found at index 26"}}}]},"status":400}

But if I change "include_upper":true to "include_upper":false, it's works.

{                                                      
   "from":0,
   "size":999,
   "query":{
      "bool":{
         "filter":[
            {
               "range":{
                  "date_created":{
                     "from":"2020-09-01T23:59:59.000-03:00",
                     "to":"2020-09-01T23:59:59.000-03:00",
                     "include_lower":true,
                     "include_upper":false,
                     "format":"8uuuu-MM-dd'\''T'\''HH:mm:ss.SSSX||uuuu-MM-dd'\''T'\''HH:mm:ss.SSSXXX||uuuu-MM-dd'\''T'\''HH:mm:ss.SSSXXXXX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSXXX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSXXXXX",
                     "boost":1.0
                  }
               }
            }
         ],
         "adjust_pure_negative":true,
         "boost":1.0
      }
   },
   "sort":[
      {
         "date_created":{
            "order":"desc"
         }
      }
   ]
} 

ES version: 6.8.4.

Could you please help me to understand this issue?
Thanks!

[Emanuil (Emanuil Tolev)]

It does indeed fail with that error on 6.8.4 exactly as you describe, and works when include_upper is set to false. I found this old deprecation notice (end of page) in the 0.90 docs stating that from, to, include_upper and include_lower were deprecated in favour of gt, gte, lt and lte.

I then read your query more closely and I'm curious, what are you trying to query exactly? Your from and to are the same moment in time. That's not really a range query :).

The error is saying that your own desired time period can't be parsed with any of the formats you're providing. I can't find any reference to the X date format marker you're using either in the Joda docs or our own. It seems to be a valid marker so chalk that down to my lack of experience with Joda.

I rewrote your query to what I think retains your intent but uses less complex formatting:

{ 
   "from":0,
   "size":999,
   "query":{
      "bool":{
         "filter":[
            {
               "range":{
                  "date_created":{
                     "lte":"2020-09-01T23:59:59.000",
                     "gte":"2020-09-01T23:59:59.000",
                     "format":"date_hour_minute_second_millis",
                     "time_zone": "-03:00",
                     "boost":1.0
                  }
               }
            }
         ],
         "adjust_pure_negative":true,
         "boost":1.0
      }
   },
   "sort":[
      {
         "date_created":{
            "order":"desc"
         }
      }
   ]
}

Let me know if that works, and if it is equivalent to your original query.

[silvina]

Thanks for getting back to me
The range before was just a test, the real range is:

               "range":{
                  "date_created":{
                     "gte":"2020-06-01T00:00:00.000-03:00",
                     "lte":"2020-09-01T23:59:59.000-03:00",
                     "format":"8uuuu-MM-dd'\''T'\''HH:mm:ss.SSSX||uuuu-MM-dd'\''T'\''HH:mm:ss.SSSXXX||uuuu-MM-dd'\''T'\''HH:mm:ss.SSSXXXXX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSXXX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSXXXXX",
                     "boost":1.0
                  }
               }
            }

This is not a joda time format, it's a date time format ( we are using this doc to change joda formatter to date time formatter: https://www.elastic.co/guide/en/elasticsearch/reference/7.x/migrate-to-java-time.html)
I think, it's not a format problem because if you change "lte" to "lt" it's works.
This is Another test that I made and it's worked:

{
               "range":{
                  "date_created":{
                     "gte":"2020-09-01T23:59:59.000-03:00",
                     "format":"8uuuu-MM-dd'\''T'\''HH:mm:ss.SSSX||uuuu-MM-dd'\''T'\''HH:mm:ss.SSSXXX||uuuu-MM-dd'\''T'\''HH:mm:ss.SSSXXXXX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSXXX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSXXXXX",
                     "boost":1.0
                  }
               }
            }

I think there is something wrong with "LTE" field in date range query. What do you think?

Thanks!

[Emanuil (Emanuil Tolev)]

This is not a joda time format, it's a date time format ( we are using this doc to change joda formatter to date time formatter: Java time migration guide | Elasticsearch Guide [7.16] | Elastic)

Thanks a lot, I forgot we did indeed switch time formats in 7.x!

it's not a format problem

Agreed. I can reproduce it - changing lte to lt in the first query here makes it work. Changing gte to gt has no effect.

For me, using this rewrite of your format string works:

"8uuuu-MM-d'T'HH:mm:ss.SSSXXXXX||uuuu-MM-d'T'HH:mm:ss.SSSXXX||uuuu-MM-d'T'HH:mm:ss.SSSX||uuuu-MM-dd'T'HH:mm:ss.SSSXXXXX||uuuu-MM-dd'T'HH:mm:ss.SSSXXX||uuuu-MM-dd'T'HH:mm:ss.SSSX"

I haven't touched your format strings, but I have re-ordered them.

You have 6 defined in this order in your format field:

uuuu-MM-dd'T'HH:mm:ss.SSSX
uuuu-MM-dd'T'HH:mm:ss.SSSXXX
uuuu-MM-dd'T'HH:mm:ss.SSSXXXXX
uuuu-MM-d'T'HH:mm:ss.SSSX
uuuu-MM-d'T'HH:mm:ss.SSSXXX
uuuu-MM-d'T'HH:mm:ss.SSSXXXXX

Mine are reordered so that the more specific format strings come before the less detailed format strings:

uuuu-MM-d'T'HH:mm:ss.SSSXXXXX
uuuu-MM-d'T'HH:mm:ss.SSSXXX
uuuu-MM-d'T'HH:mm:ss.SSSX
uuuu-MM-dd'T'HH:mm:ss.SSSXXXXX
uuuu-MM-dd'T'HH:mm:ss.SSSXXX
uuuu-MM-dd'T'HH:mm:ss.SSSX

It seems you ran into a version of this bug (+ the reorder workaround).

The bug should be fixed in 7.5.0. I tested myself in 7.9.1 (latest) and both format definitions work now. Let me know if using my format definition fixes lte on your system too.

[silvina]

It's works
thanks!