How to write Azure Cosmos DB SQL-API count distinct query without non-deterministic results

Question

My Aim

I would like to count the number of distinct values of FileName for Azure Cosmos DB documents like the following in a single partition, using the SQL API.

{
    "id": "some uuid",
    "FileName": "file-1.txt",
    "PartitionKeyField": "some key",
    ... other fields ...
}

My Test

I have uploaded 533,956 documents with 500,000 different FileName values, i.e. 33,956 documents have duplicate FileName (other fields are different). These are all uploaded with the same PartitionKeyField.

(I can only reproduce the behaviour below for 100,000s of documents).

I would like to count the number of distinct FileName values - so hope to get back 500,000.

Attempt 0 - Sanity Check

If I run the following query:

SELECT DISTINCT c.FileName 
FROM c 
WHERE c.PartitionKeyField = 'some key'

This returns 500,000 documents as expected.

Attempt 1

However, I don't need all the documents, I just need the count, so I try to run the following query

SELECT VALUE COUNT(1) 
FROM (
    SELECT DISTINCT c.FileName 
    FROM c 
    WHERE c.PartitionKeyField = 'some key'
) c2

But this gives 533,956 - i.e. it's as if DISTINCT has not been applied.

Attempt 2

Next I tried the following, redundant GROUP BY in an attempt to force the count to work:

SELECT c2.PartitionKeyField, COUNT(1) 
FROM (
    SELECT DISTINCT c.FileName 
    FROM c
    WHERE c.PartitionKeyField = 'some key'
) c2 
GROUP BY c2.PartitionKeyField

The result returned by this depends on how many RUs is allocated to the collection, e.g.

Returns 500,007 at 9900 RUs
Returns 500,175 at 5000 RUs
Returns 500,441 at 3000 RUs
Returns 500,812 at 1000 RUs
Returns 501,406 at 400 RUs

Also, the above values are averages, e.g. for 9900 RUs results of 500,009 and 500,006 were also returned.

Questions

Is it possible to write the required "count" query in a deterministic way that doesn't depend on the number of RUs? (other than retrieving all documents as in Attempt 0?)
Why does increasing the number of RUs change the result of the query in Attempt 2?

Steve Zhao Steve Zhao · Accepted Answer · 2020-11-11T01:33:03

Please try this SQL:

SELECT VALUE COUNT(c2)
FROM (
    SELECT DISTINCT c.FileName 
    FROM c 
    WHERE c.PartitionKeyField = 'some key'
) c2

How to write Azure Cosmos DB SQL-API count distinct query without non-deterministic results

1 Answers