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.