Nullable DateTime not correctly being queried in Solr after upgrade to 9.2

Question

I've inherited a Sitecore site that I'm upgrading from 8.2 to 9.2. I've noticed that the query being built in 9.2 is slightly different to the one in 8.2.

There are two relevant fields that get indexed, Date and EndDate. Date is a DateTime and EndDate is a Nullable[DateTime].

Both the 8.2 and 9.2 indexes are indexing the fields correctly as can be seen here:

However, the query generated by Sitecore is not correct as the tdt is not added to the end of the nullable type, but does work for the struct.

["undefined field: "end_date""]

[(((_template:(3da0703f1b6c4c73825d15218599f485) OR _template:(23e003536083498da8880acd4890b9e4)) AND (-_isstandardvalues_b:(True) :)) AND (date_tdt:[2020-04-26T23:00:00Z TO *] OR end_date:[2020-04-26T23:00:00Z TO *]))]

The exact same code works fine in 8.2, is this a bug or has something changed in 9.2?

Answer 1

Not sure how it was handled in Sitecore version 8.2 but for Sitecore 9.2 sometimes you want to run queries for null or empty string values. Sitecore supports this in the LINQ layer and in the indexing layer by storing physical values of EMPTYVALUE and NULLVALUE. Sitecore translates this in the LINQ layer, so all you need to do is write LINQ queries as usual.

You must specify which fields you want to have an empty string and null support for in the FieldMap. OOTB Sitecore does not store empty strings and nulls in your index because they waste space. To specify a NULLVALUE Field, you need to add it in <fieldNames hint="raw:AddFieldByFieldName"> section of your Solr search default config or in a patch config. For your question you can add below line in it for end_date -

<fieldType fieldName="end_date" returnType="datetime" nullValue="NULLVALUE" emptyString="EMPTYVALUE"/>

Make sure to add nullValue="NULLVALUE" and emptyString="EMPTYVALUE" attributes.