Sharepoint 2007 MOSS Content Query / Content Query Web Part Bug

Question

 

 

MOSS / Sharepoint 2007 Content Query / Content Query  Bug 

There is a bug in the content query and / or ContentType field assignments which produces incorrect results in the Content Query Web Part

• When you create a list fields/content fields are assigned to specific columns (Varchar1,varchar2, date1, date2 etc ) in the AddUserData table in the Sharepoint Content database. 
• This mapping of field to column in stored in the field schema or tp_Fields in the AllLists table.
• As every list has at least one content , these fields will  naturally get the first slots in the AddUserData table.
• If you start adding content types to different Lists or Doc Libraries the content types fields will be added in the next available slots in the tables but there is no guarantee that for any content type field in a list that it will occupy the same field in the AddUserData table as the same content type in another list.  
• The content query works by querying a set of lists and for the columns in the AllUsersData determines which fields in each list corresponds to what columns in the table.
• So far so good, however the generated where clause is wrong and expects the fields to be in the same columns which is only true if each list is created in the same manner applying the content types in the same way for each list.

Here is the SQL where  clause generated by a content query. Notice it references the UserData.[datetime4] specific field

  WHERE ((UserData.[tp_ContentType] = N''xCorp_cTypes_NewsPublishing'') AND (((UserData.[tp_ModerationStatus] = 0) AND (UserData.[datetime4] <= @L3DTP)) and (UserData.[datetime5] >= @L4DTP)))

Now here are the columns in the select clause

,CASE WHEN ListOrds.ColumnOrd29=2 THEN UserData.[datetime6] WHEN ListOrds.ColumnOrd29=1 THEN UserData.[datetime4] END AS QryCol28,

CASE WHEN ListOrds.ColumnOrd30=1 THEN UserData.[datetime5] WHEN ListOrds.ColumnOrd30=2 THEN UserData.[datetime7] END AS QryCol29

Here the content query is selecting the appropriate data column depending on which list it is reading, for some lists a particular content date field is in datetime6 and in others it is in datetime4.

But the where clause is only applied to datetime4…. !!! 

 This is got to be a bug.   Basically you can’t trust any filtered content  query!!!

This MOSS SP1, Anyone see this in pre sharepoint SP0?

 

 

http://znetsys.spaces.live.com/

 

 

Answer 1

Do you have more than 9 document libraries in your site collection?

Answer 2

Sure, many sites with at least one or two doc libraries (or libraries that inherit from doc library (x0101)) in each.   

 

http://znetsys.spaces.live.com/

 

 

Answer 3

There is a known bug when using the SPSiteDataQuery class and your site collection has more than 9-10 document libraries/lists. If you do not limit your query to 9 or less document libraries using the <Lists> node in the query then the CAML query engine will escalate to use a temporary table. It then expects to have the columns in the same order across all the libraries, which sounds very similar to your problem. I could guess that the content query webpart is using the SPSiteDataquery class under the covers. You could use reflector to confirm this. You can read more about the bug at this link.

 

http://www.sharepointblogs.com/smc750/archive/2007/07/24/spsitedataquery-limited-to-10-document-libraries-or-lists.aspx

Answer 4

 

Thanks,

You are describing the same disease as I was getting. 

 

But I have a solution for the Content Query Issue here on CodePlex.

http://www.codeplex.com/SPCQOptimizer/Wiki/View.aspx?title=Home

 

Thanks