I have an SPQuery that has been working fine until my list hit 500 items:
q.Query = "<Where><Eq><FieldRef Name='DefectID' /><Value Type='Counter'>" + DefectID + "</Value></Eq></Where>";
This was successfully returning 1 item where DefectID matched my provided value.
Once the list hits 500 items, the query returns 0 items, no error message, no exception. If I delete 1 item and take the list down to 499 items, it works again.
The list had individual item permissions, but as part of my debugging I have reset the role inheritance of the list and also every item in the list.
I have tried providing a row limit of 2, and 2000, same result.
Have tried Query override throttling, same result. Changed the web app throttle limits to way above my lists number of items, and change the large query window to 24hrs, same result.
The column I am querying has an index on it (I am expecting about 30k+ items per year in this list). If I delete the index the query then works when items in the list are > 500. If I create the index again, the query then returns 0.