Weird Filtered List Problem with 500 items

Question

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.

Answer 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.

Answer 2

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.