0

I have a weird problem in SharePoint 2013 when using a filter on a standard list in it's standard "all items" view or any other custom view.

I have 499 items in the list and everything works fine. As soon as I hit 500 items the filter produces weird duplicate results.

Background:

  • When in the standard "All Items" view I can view all items "with no duplicates" for both 499 and 500 items
  • I can even sort on columns for both 499 and 500 items just fine and it produces the correct results (no duplicates)
  • With 499 items I can provide a filter on a column and get back 3 results (as expected)... yay!
  • With 500 items I provide the same filter and get back 20+ results (all being duplicates of the 3 that were expected)... not yay!
  • I know they are duplicates as the item Ids are the same when viewing the URL

Things I have tried:

  • Recreating the list with the exact data
  • Recreating the views
  • Indexing the column
  • Changing the threshold limits
  • Changing the item view limits
  • Grouping different columns
  • Changing a view to hold only the 1 column that I want to filter

Nothing has worked

I'm hoping that there is a web.config or administrator setting I can change that somehow has limited this.

Any suggestions are welcome.

Thanks in advance.


ADDITIONAL INFO:

After inspecting the SQL in profiler it seems that once I hit 500 items a modified query is run. Specifically the following join (which isn't there when I have 499 items) gets added:

FROM AllUserData AS t2 WITH(NOLOCK) 
INNER JOIN NameValuePair AS NVP WITH(FORCESEEK(NameValuePair_CI(SiteId,ListId,FieldId))) 
ON (NVP.SiteId = @SITEID) 
AND (NVP.FieldId = @L6) 
AND (NVP.Value = t2.tp_ID) 
AND (t2.tp_SiteId = NVP.SiteId) 
AND (t2.tp_ListID = @L2) 
AND (NVP.ListId = @LISTID) 
AND (t2.tp_RowOrdinal = 0) 

In the end all I need it to do is add a 'distinct' to the main select and it would work again.


STATUS UPDATE:

Looks like I've been able to replicated the problem. I have submitted a bug request to Microsoft... Will update when I find out some more answers.

1

Have you created the list using a SP2010 list template ? Is there any additional columns that is having the same display name ? Also I faced a similar issue when my system resources where low on my VM. Not sure whether it is related.

  • Hi, the list was not created in SP2010. Freshly created in SP2013. No other columns have the same display name in this list. Resources are not an issue the SQL box has 256GB Ram. As mentioned in the additional info it looks like the actual query is being changed once I hit 500 items. – userCP Sep 24 '14 at 5:50
-1

Updating to SP1 fixed the issue. After explaining the problem to Microsoft technical support they created a lab and applied my step by step instructions on how to replicate the issue. Their SP1 version didn't have the issue, thus it was concluded that the actual bug was fixed. I installed SP1 on my dev machine and it did in fact fix the issue.

The cause was related to versioning. Duplicates were appearing due to multiple versions of an item on a lookup column. This issue only came about due to the lookup column being indexed and the parent list on the lookup being versioned and the items on that list being edited to contain versions.

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.