Raw Lucene Date Range Query not working in Examine (Umbraco 8)

[system]

For Examine experts!

Have a strange issue with Examine in Umbraco 8. If I use the fluent API to create a RangeQuery using dates then it works, but if I execute the same query as a raw query then it doesn’t. To provide an example:

if (ExamineManager.Instance.TryGetIndex(global::Umbraco.Core.Constants.UmbracoIndexes.ExternalIndexName, out var index))
{
    var searcher = index.GetSearcher();
    var query = searcher.CreateQuery("content").RangeQuery<DateTime>(new string[] { "createDate" }, DateTime.Now.AddDays(-37), DateTime.Now);

    var results = query.Execute();

    // results.TotalItemCount = 2;
}

So the above query returns 2 results, which is correct. It just returns items with a create date between the two specified dates, as expected.

However, if I take the raw Lucene query from the above code (eg. via query.ToString()) I get this:

+(createDate:[637109872908600000 TO 637141840908600000])

(Note the date ticks values will change depending on what the current date is - this is just an example).

Now if I run this in the back-office Examine Management dashboard it returns every node, even if I amend it just to search content. So my query would actually be:

+__IndexType:content +createDate:[637109872908600000 TO 637141840908600000]

This still returns every content node in the site.

In fact, even this query below returns every node when I would expect it to return none (as the ‘from’ date is after the ‘to’ date).

+__IndexType:content +createDate:[999999999999999999 TO 637143565034990000]

I’ve run these queries in the back office Examine Management dashboard and also via Luke.Net to same effect. It’s like the range query is ignored.

So my question is why does the date range query work when constructed via the fluent API, but not if constructed manually as a raw query?

Is there any way to get the raw query to work? (I’m building a rather complex query via a raw Lucence query and it works fine apart from this date range part). Note I’m searching the built-in createDate field, not a custom field, and I can see the date is being stored as ticks in the index. I’m using the Standard Analyser, too.

---

This is a companion discussion topic for the original entry at https://our.umbraco.com/forum/100637-raw-lucene-date-range-query-not-working-in-examine-umbraco-8

[wtct (Wojciech Tengler)]

Hi @Shazwazza,

I have experienced the same issue in the Umbraco v13. Therefore, it seems it’s still a valid issue and it’s worth continuing this thread here.

However, I’m not sure whether it’s a bug or misunderstanding of the raw query and the format of date which should be used in the range query.

We can see that dates are stored as rounded ticks and it’s really confusing.

As you mentioned earlier, the raw query is being parsed and the date in ticks is not interpreted correctly, so finally ticks are replaced with asterisks. I have confirmed that by the following piece of code:

var mediaNativeQuery = index.Searcher.CreateQuery(IndexTypes.Media)
	.NativeQuery($"+__NodeTypeAlias:folder +createDate:[{638882565060080000} TO {638882565060080000}]");

string rawQuery = mediaNativeQuery.ToString()

Unfortunately, the value of the rawQuery variable will be as follows:

{ Category: media, LuceneQuery: +(+__NodeTypeAlias:folder +createDate:[* TO *]) }

In the example above, we are retrieving all folders regardless of the createDate.

It means that we have to rely on the ISO-8601 format for dates and the raw query is not fully raw, because it’s still being interpreted.

Besides, we are not able to prepare a raw query by a query builder and then execute it as a native query. I know, it sounds ridiculous to do that, but I have a corner case in which it would be valuable to have such a possibility due to another bug I will report at some point.

Please let me know if there is anything we can do with this issue.

[mistyn8 (Mike Chambers)]

if you set logging to debug it reveals I think what you’ve discovered…

[19:01:21 DBG] An conversion error occurred with outputConverter.ConvertFrom from 637109872908600000 to System.DateTime
System.FormatException: 637109872908600000 is not a valid value for DateTime.
 ---> System.FormatException: String '637109872908600000' was not recognized as a valid DateTime.
   at System.DateTimeParse.Parse(ReadOnlySpan`1 s, DateTimeFormatInfo dtfi, DateTimeStyles styles)
   at System.DateTime.Parse(String s, IFormatProvider provider)
   at System.ComponentModel.DateTimeConverter.ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, Object value)
   --- End of inner exception stack trace ---
   at System.ComponentModel.DateTimeConverter.ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, Object value)
   at Examine.Lucene.Indexing.IndexFieldValueTypeBase.TryConvert[T](Object val, T& parsedVal)
[19:01:21 DBG] An conversion error occurred with outputConverter.ConvertFrom from 637141840908600000 to System.DateTime
System.FormatException: 637141840908600000 is not a valid value for DateTime.
 ---> System.FormatException: String '637141840908600000' was not recognized as a valid DateTime.
   at System.DateTimeParse.Parse(ReadOnlySpan`1 s, DateTimeFormatInfo dtfi, DateTimeStyles styles)
   at System.DateTime.Parse(String s, IFormatProvider provider)
   at System.ComponentModel.DateTimeConverter.ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, Object value)
   --- End of inner exception stack trace ---
   at System.ComponentModel.DateTimeConverter.ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, Object value)
   at Examine.Lucene.Indexing.IndexFieldValueTypeBase.TryConvert[T](Object val, T& parsedVal)

[wtct (Wojciech Tengler)]

Hi, good to know, because the log viewer is the last thing I would check in this case. Moreover, I had to look for this specific error, because in the debug mode it’s hard to find anything

Probably it would be better to not catch this exception and throw it instead of returning unexpected search result.

Anyway, in my opinion the raw query obtained by ToString() extension should be consistent with the query which we should pass to the NativeQuery method regardless how it works under the hood.

[mistyn8 (Mike Chambers)]

PS you can work around this by passing a datetime string value that the native asp.net core datetime is able to parse.. eg

+(createDate:[2025-01-01 TO 2025-10-02])

or
+(createDate:[2025-02-03 10:00:00 TO 2025-02-04 11:00:00])

[wtct (Wojciech Tengler)]

Exactly @mistyn8, I have figured that out during my investigation. In the previous message I was just referring that I was not aware about the log entry which is being added in this case.

BTW, I would like to mention that this thread is just a continuation of the previous thread:

https://our.umbraco.com/forum/100637-raw-lucene-date-range-query-not-working-in-examine-umbraco-8

I suppose that there is still a chance to resolve the inconsistency in the Examine. As you probably noticed, @Shazwazza prepared a fix, but has not pushed it yet, because it’s not fully completed

I know that thread is a bit outdated, but the issue is still current.

Basically, it would be nice to have a consistency in date formats between the query we need to pass to the NativeQuery method and the query which we can retrieve by the ToString() extension.

I hope it makes sense.