Query not returning documents

[kedzior-io]

trafficstars

Using: v0.9.3-beta

I have been using this tool for a year now and it has been great! Today I found an interesting issue. Quering certain document I knew it exists would not be returned with CosmosDBExplorer:

Same query returns document when run via Data Explorer in Azure Portal.

[sachabruttin]

• [ ] Are you able to debug (with VS .NET or VS Code) the method ExecuteQueryAsync()
• [ ] Did you check that you didn't have defined a "Partition Key" on the toolbar?
• [ ] Can you share the Response Header tab content?

[PeterMacko]

Hello @sachabruttin,

im facing exact same issue. In our system we have stored transactions. All transaction has cardId, transactionId and lot of other parameters. Sometimes when we try to search by transactionId we get no results in CosmosDbExplorer, but when we copy paste same query to Azure Cosmos DB we get one transaction item returned. If I try to search for all transactions for cardId I get list of transactions where Im possible to find transaction which Im looking for also in Cosmos DB Explorer.

So I download your code and deep into debugging. The behavior is strange, because I get 0 items in FeedResponse but resultSet.HasMoreResults has value true. I try to implement while loop on HasMoreResults property, but this will run infinitely and in all iteration I get 0 items. The exact same behavior is described: https://github.com/Azure/azure-cosmos-dotnet-v2/issues/376 and https://stackoverflow.com/questions/55942034/why-does-cosmosdb-feedresponse-contain-no-results-when-hasmoreresults-is-true?rq=4. I also try to write minimal code:

CosmosClient client = new(
  accountEndpoint: "url",
  authKeyOrResourceToken: "key"
);

var cont = client.GetContainer("someContainer","transactions");

var querydef = new QueryDefinition("select * FROM transactions AS t where t.uniqueTransactionId = 'trans123'");
using (var feedIterator = cont.GetItemQueryIterator<JToken>(querydef))
{
  while (feedIterator.HasMoreResults)
  {
     var response = await feedIterator.ReadNextAsync();
  }
}

but the behavior is the same - infinity loop on HasMoreResults and no items in response

if I try to rewrite query to count than I get result 1. So the item is there.

the response header for Select * query is:

{
  "Content-Length": "49",
  "Content-Type": "application/json",
  "date": "Wed, 09 Aug 2023 21:40:02 GMT",
  "lsn": "2944014",
  "Server": "Compute",
  "x-ms-activity-id": "c79b8358-1c5b-4bb8-92fa-06d2bff6e0bd",
  "x-ms-alt-content-path": "dbs/SPL-PRL/colls/transactions-eun",
  "x-ms-content-path": "Z5hmANg5toU=",
  "x-ms-continuation": "[{\"token\":null,\"range\":{\"min\":\"05C1853DB928\",\"max\":\"05C1953DB928\"}}]",
  "x-ms-cosmos-index-utilization": "eyJVdGlsaXplZFNpbmdsZUluZGV4ZXMiOlt7IkZpbHRlckV4cHJlc3Npb24iOiIiLCJJbmRleFNwZWMiOiJcL3VuaXF1ZVRyYW5zYWN0aW9uSWRcLz8iLCJGaWx0ZXJQcmVjaXNlU2V0Ijp0cnVlLCJJbmRleFByZWNpc2VTZXQiOnRydWUsIkluZGV4SW1wYWN0U2NvcmUiOiJIaWdoIn1dLCJQb3RlbnRpYWxTaW5nbGVJbmRleGVzIjpbXSwiVXRpbGl6ZWRDb21wb3NpdGVJbmRleGVzIjpbXSwiUG90ZW50aWFsQ29tcG9zaXRlSW5kZXhlcyI6W119",
  "x-ms-cosmos-is-partition-key-delete-pending": "false",
  "x-ms-cosmos-llsn": "2944014",
  "x-ms-cosmos-physical-partition-id": "0",
  "x-ms-cosmos-query-execution-info": "{\"reverseRidEnabled\":false,\"reverseIndexScan\":false}",
  "x-ms-documentdb-partitionkeyrangeid": "0",
  "x-ms-documentdb-query-metrics": "totalExecutionTimeInMs=0.30;queryCompileTimeInMs=0.04;queryLogicalPlanBuildTimeInMs=0.01;queryPhysicalPlanBuildTimeInMs=0.04;queryOptimizationTimeInMs=0.00;VMExecutionTimeInMs=0.05;indexLookupTimeInMs=0.04;instructionCount=5;documentLoadTimeInMs=0.00;systemFunctionExecuteTimeInMs=0.00;userFunctionExecuteTimeInMs=0.00;retrievedDocumentCount=0;retrievedDocumentSize=0;outputDocumentCount=0;outputDocumentSize=49;writeOutputTimeInMs=0.00;indexUtilizationRatio=0.00",
  "x-ms-gatewayversion": "2.0.0",
  "x-ms-global-Committed-lsn": "2944013",
  "x-ms-item-count": "0",
  "x-ms-last-state-change-utc": "Sat, 05 Aug 2023 16:28:16.281 GMT",
  "x-ms-number-of-read-regions": "1",
  "x-ms-request-duration-ms": "0.609",
  "x-ms-request-charge": "2.8",
  "x-ms-resource-quota": "documentSize=51200;documentsSize=52428800;documentsCount=-1;collectionSize=52428800;",
  "x-ms-resource-usage": "documentSize=10482;documentsSize=9940046;documentsCount=3010823;collectionSize=10733926;",
  "x-ms-serviceversion": "version=2.14.0.0",
  "x-ms-session-token": "0:0#2944014#2=-1",
  "x-ms-schemaversion": "1.16",
  "x-ms-substatus": "0",
  "x-ms-transport-request-id": "1",
  "x-ms-xp-role": "2"
}

now im stuck. its looks like that there is problem with implementation in MS.Cosmos package. And there is no problem in implementation of Cosmos DB Explorer. Maybe you get some idea with this information.

[sachabruttin]

Hello @PeterMacko, thanks for the information.