Azure, DocumentDB, Count give incorrect results

Question

On a documentDB collection, in the portal I run the next query:

SELECT * FROM c WHERE NOT IS_DEFINED(c.UserDataType)

Some results returned.

Now running this query:

SELECT count(1) FROM c WHERE NOT IS_DEFINED(c.UserDataType)

return this results:

[ { "$1": 0 } ]

how can it be ?

One thing that may help is that when query is running in the portal a few Continuation happens

I just repeated the same query on my collections and they worked. Are you using multiple partitions? Do you have multiple geographical regions? If so, which is your default consistency? — Matias Quaranta
How many records/documents in that collection? Do you try to run same query on another collection? — Fei Han
How do I tell if it is multiple partition ? It is not multiple geographic regions. — Guy Assaf

Aravind Krishna R. Aravind Krishna R. · Accepted Answer · 2017-04-10T23:00:37

DocumentDB is different from conventional databases in two ways - 1) it has a latency cap of 5 seconds for all requests since it's a cloud-based service based on HTTPS and REST, and 2) it's a database with provisioned throughput, so you get predictable performance (which is great) but have to execute queries within a reserved budget of resources.

This means that some queries can make incremental progress, and you have to resume execution by resubmitting the query with a continuation token until all results are available. For aggregation queries, DocumentDB works like "map-reduce" in that partial aggregate results are returned to the client, and the client is responsible for producing the final result (e.g. summing the aggregates). Normally, you wouldn't notice this behavior because queries complete in one round trip, but you would notice when the query requires a scan for execution (like in this cause because it involves a negation with the NOT IS_DEFINED clause).

If you run the query to completion, you will see the correct results returned.

Azure, DocumentDB, Count give incorrect results

1 Answers