Why does my Azure Cosmos query return empty results when it should return many results?

Question

I am running a query against my Cosmos db instance, and I am occasionally getting 0 results back, when I know that I should be getting some results.

        var options = new QueryRequestOptions()
        {
            MaxItemCount = 25
        };
        var query = @"
                    select c.id,c.callTime,c.direction,c.action,c.result,c.duration,c.hasR,c.hasV,c.callersIndexed,c.callers,c.files
                    from c
                    where
                    c.ownerId=@ownerId
                    and c.callTime>=@dateFrom
                    and c.callTime<=@dateTo
                    and (CONTAINS(c.phoneNums_s, @name)
                    or CONTAINS(c.names_s, @name)
                    or CONTAINS(c.xNums_s, @name))
                    order by c.callTime desc";
        var queryIterator = container.GetItemQueryIterator<CallIndex>(new QueryDefinition(query)
        .WithParameter("@ownerId", "62371255008")
        .WithParameter("@name", "harr")
        .WithParameter("@dateFrom", dateFrom) // 5/30/2020 5:00:00 AM +00:00
        .WithParameter("@dateTo", dateTo) // 8/29/2020 4:59:59 AM +00:00
        .WithParameter("@xnum", null), requestOptions: options, continuationToken: null);
        if (queryIterator.HasMoreResults)
        {
            var feed = queryIterator.ReadNextAsync().Result;
            model.calls = feed.ToList(); //feed.Resource is empty; feed.Count is 0;
            model.CosmosContinuationToken = feed.ContinuationToken; //feed.ContinuationToken is populated with a large token value, indicating that there are more results, even though this fetch returned 0 items.
            model.TotalRecords = feed.Count(); // 0
        }

As you can see, even though I received 0 results, the continuation token indicates that there is more data there after this first request. And, after visually inspecting the data directly in the database (data explorer in the Azure portal), I see records that should match, but they are not found in this query. To further test, I ran the same exact query a few seconds later, and received results:

        var query = @"
                    select c.id,c.callTime,c.direction,c.action,c.result,c.duration,c.hasR,c.hasV,c.callersIndexed,c.callers,c.files
                    from c
                    where
                    c.ownerId=@ownerId
                    and c.callTime>=@dateFrom
                    and c.callTime<=@dateTo
                    and (CONTAINS(c.phoneNums_s, @name)
                    or CONTAINS(c.names_s, @name)
                    or CONTAINS(c.xNums_s, @name))
                    order by c.callTime desc";
        var queryIterator = container.GetItemQueryIterator<CallIndex>(new QueryDefinition(query)
        .WithParameter("@ownerId", "62371255008")
        .WithParameter("@name", "harr")
        .WithParameter("@dateFrom", dateFrom) // 5/30/2020 5:00:00 AM +00:00
        .WithParameter("@dateTo", dateTo) // 8/29/2020 4:59:59 AM +00:00
        .WithParameter("@xnum", null), requestOptions: options, continuationToken: null);
        if (queryIterator.HasMoreResults)
        {
            var feed = queryIterator.ReadNextAsync().Result;
            model.calls = feed.ToList(); //feed.Resource has 25 items; feed.Count is 25;
            model.CosmosContinuationToken = feed.ContinuationToken; //feed.ContinuationToken is populated, but it is considerably smaller than the token I received from the first request.
            model.TotalRecords = feed.Count(); // 25
        }

This is the exact query as before, but this time the feed gave me the results I expected. This has happened more than once, and continues to happen intermittently. What gives with this? Is this a bug in Azure Cosmos? If so, it seems like a serious bug that breaks the very core functionality of Cosmos (and databases in general).

Or, is this expected? Is it possible that in the first query, I need to continue to ReadNextAsync until I get some results back using the continuation token?

Any help is appreciated, as this is breaking very basic functionality in my app.

Also, I would like to add that the data returned from the query has not been newly added between the times of my first query attempt, and my second query attempt. That data has been there for a while.

Answer 1

Your code is correct, you are expected to drain the query checking HasMoreResults (although I would change the .Result with await to avoid a possible deadlock). What can happen in cross-partition queries is that you could get some empty page if the initial partitions checked for results have none.

Sometimes queries may have empty pages even when there are results on a future page. Reasons for this could be:

• The SDK could be doing multiple network calls.
• The query might be taking a long time to retrieve the documents.

Reference: https://learn.microsoft.com/azure/cosmos-db/troubleshoot-query-performance#common-sdk-issues

Answer 2

Try using below code:

Query Cosmos DB method:

public async Task<DocDbQueryResult> QueryCollectionBaseWithPagingInternalAsync(FeedOptions feedOptions, string queryString, IDictionary<string, object> queryParams, string collectionName)
        {
            string continuationToken = feedOptions.RequestContinuation;
            List<JObject> documents = new List<JObject>();
            IDictionary<string, object> properties = new Dictionary<string, object>();
            int executionCount = 0;
            double requestCharge = default(double);
            double totalRequestCharge = default(double);

            do
            {
                feedOptions.RequestContinuation = continuationToken;
                var query = this.documentDbClient.CreateDocumentQuery<JObject>(
                    UriFactory.CreateDocumentCollectionUri(this.databaseName, collectionName),
                    new SqlQuerySpec
                    {
                        QueryText = queryString,
                        Parameters = ToSqlQueryParamterCollection(queryParams),
                    },
                    feedOptions)
                .AsDocumentQuery();

                var response = await query.ExecuteNextAsync<JObject>().ConfigureAwait(false);
                documents.AddRange(response.AsEnumerable());
                executionCount++;
                requestCharge = executionCount == 1 ? response.RequestCharge : requestCharge;
                totalRequestCharge += response.RequestCharge;
                continuationToken = response.ResponseContinuation;
            }
            while (!string.IsNullOrWhiteSpace(continuationToken) && documents.Count < feedOptions.MaxItemCount);

            var pagedDocuments = documents.Take(feedOptions.MaxItemCount.Value);
            var result = new DocDbQueryResult
            {
                ResultSet = new JArray(pagedDocuments),
                TotalResults = Convert.ToInt32(pagedDocuments.Count()),
                ContinuationToken = continuationToken
            };

            // if query params are not null, use existing query params also to be passed as properties.
            if (queryParams != null)
            {
                properties = queryParams;
            }

            properties.Add("TotalRequestCharge", totalRequestCharge);
            properties.Add("ExecutionCount", executionCount);

            return result;
        }

ToSqlQueryParamterCollection method:

private static SqlParameterCollection ToSqlQueryParamtereCollection(IDictionary<string, object> queryParams)
        {
            var coll = new SqlParameterCollection();
            if (queryParams != null)
            {
                foreach (var paramKey in queryParams.Keys)
                {
                    coll.Add(new SqlParameter(paramKey, queryParams[paramKey]));
                }
            }

            return coll;
        }