Why Your CAML Query Might Be Returning No Results

A curious thing.

We were working on a custom view for one of our lists and found that we were not able to retrieve a list item using a CAML query that should have worked.

After spending quite a bit of time studying the query itself, I decided to look at the actual SQL that was being generated.

This list has a large number of columns and SharePoint was wrapping the rows according to the boundary conditions.  For example, when you have 16 integer or user fields, the 17th field of this type will force the row to wrap into a second row.

When this occurs, in the AllUserData table, you will see multiple rows for the same item, each with a different tp_RowOrdinal. Looking in the table, it was clear that the value was in the table, but it was not on the first row (tp_RowOrdinal=”0″).

The problem with this is that for some odd reason, the SQL query that gets generated when you query on the field that has wrapped onto the second row inexplicably only queries the first row.

In the generated SQL, I found the following:

In this case, “34” represents the ID of a user and “int10” represents the column that the data is in.  But the key is that even though this data occurred in the row with ordinal 2, the SQL will filter only on the row with ordinal 0.

This is kind of inexplicably bad but not all is lost.

The dirty workaround is that you can do one of two things:

1. If you are adding the columns manually or in a single content type, you need to ensure that the field(s) that you want to query against appear earlier in your content type definition or they get added earlier.
2. If you are adding the columns via content types in a Schema.xml file, you need to ensure (1) above and that you add that content type to the list earlier in the XML.

There is an MSDN discussion on this, but Microsoft seems to claim that this is not a “bug” (it most certainly is).