Why does making a Lookup site column an indexed field break my CAML Query?

I created a custom timer job definition that processes email-enabled list items. Since these list items were created from email, I extract certain email header information from the email and save that information as meta-data.

One of those site columns is called EmailSource. This is a Lookup field using the Title field of the EmailSource list as the value. My timer job queries the main list to find all list items where the EmailSource IsNULL and then I extract information from the email headers, and then find the appropriate EmailSource from the EmailSource list and write it to the EmailSource field in the main list.

It has been working pretty slick for the last month or so. Recently, I thought that I would try to improve the response of the main list views by setting the EmailSource site column as an indexed column. Since most of the views are created filtering on the EmailSource field, I figured that this would be an ideal candidate for indexing. As soon as I set the EmailSource site column as an indexed column, my query for IsNull started returning 0 (zero) items, even when I could see that there were list items that didn’t have an EmailSource value.

My query is:

      <FieldRef Name='EmailSource' />

Now – normally, when the EmailSource field is NOT set as an indexed column, this query would return all list items where EmailSource didn’t have a value. As soon as I set EmailSource as an indexed column, the query would always return zero items, even though I knew it should return some list items.

As soon as I removed that field from the indexed columns list, the query began to work correctly again.

So – I ask you. Why would setting a field as an indexed column break a query?!!!!

One response to “Why does making a Lookup site column an indexed field break my CAML Query?”

Leave a Reply

Your email address will not be published. Required fields are marked *