CAML query with OR returns no result, where single part of it returns results

Question

I have a CAML query which queries 2 multi taxonomy (managed metadata) fields. I use the IN operator for this.

The query should return items where in one of the fields a match was found, so either in field 'BusinessUnit' or in field 'ItSystem' or in both, so i use OR in the query.

Unfortunately the query doesn't return any items, even though there are items matching for either part of the query:

COMBINED query with "OR" (0 results):

<Query>
    <Where>
        <Or>
            <In>
                <FieldRef LookupId='TRUE' Name='BusinessUnit' />
                <Values>
                    <Value Type='Integer'>62</Value>
                </Values>
            </In>
            <In>
                <FieldRef LookupId='TRUE' Name='ItSystem' />
                <Values>
                    <Value Type='Integer'>61</Value>
                </Values>
            </In>
        </Or>
    </Where>
</Query>

Query only against Business Units - 1 item as result:

<Query>
    <Where>
        <In>
            <FieldRef LookupId='TRUE' Name='BusinessUnit' />
            <Values>
                <Value Type='Integer'>62</Value>
            </Values>
        </In>
    </Where>
</Query>

Query only against IT systems - multiple items as result:

<Query>
    <Where>
        <In>
            <FieldRef LookupId='TRUE' Name='ItSystem' />
            <Values>
                <Value Type='Integer'>61</Value>
            </Values>
        </In>
    </Where>
</Query>

So, how can that be that the combination doesn't return any results where the single parts would return results? I tested it directly in SharePoint with CSOM and also with the tool SPQueryBuilderOnline, same result - and no error message.

I initially thought it may have something todo with the "List View Lookup Threshold" limit as the list does have many lookup fields - but I raised it for tests to 28 and made an IISRESET and nothing changed. Still 0 results.

When I change the query to use the EQ operator instead of IN and use only the VALUE line an remove the VALUES lines it also works, but of course this would not work when I want to query multiple values (and I will have to).

Any ideas?

Answer 1

Try rewriting the query using the equals operator. Also note the Query tag is not required if you're not using the client object model:

<Where>
    <Or>
        <Eq>
            <FieldRef LookupId='TRUE' Name='BusinessUnit' />
            <Value Type='Integer'>62</Value>
        </Eq>
        <Eq>
            <FieldRef LookupId='TRUE' Name='ItSystem' />
            <Value Type='Integer'>61</Value>
        </Eq>
    </Or>
</Where>