Indexed column throws threshold error while filtering based on range

[vivekmurli]

Target SharePoint environment

SharePoint Online

What SharePoint development model, framework, SDK or API is this about?

SharePoint REST API

Developer environment

No response

What browser(s) / client(s) have you tested

• 💥 Internet Explorer
• 💥 Microsoft Edge
• 💥 Google Chrome
• 💥 FireFox
• 💥 Safari
• mobile (iOS/iPadOS)
• mobile (Android)
• not applicable
• other (enter in the "Additional environment details" area below)

Additional environment details

No response

Describe the bug / error

We have a list with over 1M items. The end goal is to filter the column InvDate based on date range.

The InvDate is an indexed single line of text column , However it throws the List View Threshold error while getting the item using RestAPI.

Code

function FilterDate()
{
  //var initialUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/GetByTitle('ClaimListNew')/Items?$select=ID,*";
  
var sdate = $('#sdate').val();
var edate = $('#edate').val();


var myQuery= _spPageContextInfo.webAbsoluteUrl +"/_api/web/lists/getbytitle('MyList')/items?$select=ID*";
myQuery+="&$filter=InvDate ge '" +sdate+ "' and InvDate le '" +edate+ "'";
myQuery+="&$top=4999";
myQuery+="&$orderby=ID desc";


// start the process
var getAllItemsRequest = GetAllItems(myQuery);

// wait for it all to complete
getAllItemsRequest.done(function (allItems) {

console.log(allItems);
   
  
    
}).fail(function (error) {
    console.warn("Error getting all items");
    console.warn(JSON.stringify(error));
});
}

Function to get all the list items

function GetAllItems(requestUrl) {
    var deferred = $.Deferred();
    $.ajax({
        url: requestUrl,
        method: "GET",
        headers: {
            "accept": "application/json;odata=verbose",
        }
    }).done(function (thisResult) {
        if (thisResult.d.__next) {
            // if the __next property is present,
            // that measn that there's more to get,
            // so don't start resolving things yet,
            // just make the next request
            var nextRequest = GetAllItems(thisResult.d.__next);
            
            // since GetAllItems returns a promise,
            // we have to wait for that promise to resolve
            // before resolving the promise we made 
            // at the beginning of this function
            nextRequest.done(function(nextResult) {
                // nextResult should be a plain array of items,
                // so we need to combine that with the array
                // of items we got in this call in thisResult.d.results
                var combinedResults = thisResult.d.results.concat(nextResult);
                
                // then resolve the combined results back up to the next level
                deferred.resolve(combinedResults);

            }).fail(function(error) {
                deferred.reject(error);
            });
        } else {
            // __next doesn't exist, so
            // there's nothing more to get, so
            // start resolving the nested promises
            deferred.resolve(thisResult.d.results);
        }
    }).fail(function (result) {
        deferred.reject(result);
    });
    return deferred.promise();
};

The above function has retrieved all 1 Million items without any filters as below

var myQuery= _spPageContextInfo.webAbsoluteUrl +"/_api/web/lists/getbytitle('ClaimlistNew')/items?$select=ID,*";

When we use query with the below filter , we get the threshold error

var myQuery= _spPageContextInfo.webAbsoluteUrl +"/_api/web/lists/getbytitle('MyList')/items?$select=ID*"; myQuery+="&$filter=InvDate ge '" +sdate+ "' and InvDate le '" +edate+ "'"; myQuery+="&$top=4999"; myQuery+="&$orderby=ID desc";

The end goal is to filter the InvDate column with the date range.

Would be grateful if anyone could let me know how this can be achieved.

Steps to reproduce

1. In RestApi , use the filter and we get a threshold error

var myQuery= _spPageContextInfo.webAbsoluteUrl +"/_api/web/lists/getbytitle('MyList')/items?$select=ID*"; myQuery+="&$filter=InvDate ge '" +sdate+ "' and InvDate le '" +edate+ "'"; myQuery+="&$top=4999"; myQuery+="&$orderby=ID desc";

2. When we use the queries without any filters , all the list Items are retrieved as below

var myQuery= _spPageContextInfo.webAbsoluteUrl +"/_api/web/lists/getbytitle('ClaimlistNew')/items?$select=ID,*";

Expected behavior

1. The column on which the filter is being applied is an indexed column
2. For the same reason , the filter applied based on range should fetch the desired result irrespective of the number of items returned , If not there should be a filter or a workaround to get the desired results

Thank you for reporting this issue. We will be triaging your incoming issue as soon as possible.

[vivekmurli]

@waldekmastykarz
@andrewconnell
@VesaJuvonen

[github-actions]

Thank you for taking the time to file an issue. We periodically archive older or inactive issues as part of our issue management process, which automatically closes them once they are archived.

If you’d like to understand more about why and how we handle archived (closed) issues, please see Our approach to closed issues.

We appreciate your contribution and if this is still an active issue with the latest SPFx versions, please do resubmit the details. We needed to perform a cleanup, so that we can start with a clean table with a new process. We apologize for the inconvenience this might cause.