Inexplicable SQL Query Result in Cosmos DB Data Explorer

Question

I have a collection with ca. 7.000 documents that have been moved over to a new database (to take advantage of Index v2 and database throughput provisioning).

The Query

SELECT VALUE COUNT(c.id)  FROM c WHERE c._DAT.KursFrei._v = "RD18-G 06"

yields the following (correct) result:

[
    72
]

yet this query for example:

SELECT DISTINCT VALUE c._DAT.KursFrei._v FROM c

only shows the following output:

[
"NFS 16 BW",
"G 04 2018",
"NFS 15",
"NFS 17 BW",
"NFS 17",
"G 05 2018",
"G 05 2015",
"RDF 07",
"RDF 07 "
]

The constrained value from the first query does not even show up.

SELECT VALUE c._DAT.KursFrei._v FROM c

only shows 149 results, while

SELECT COUNT(VALUE) c._DAT.KursFrei._v FROM c

correctly shows 6891

Cross-Partition-Query is enabled
"Query Results per Page" is set to 10000 (Unlimited still seems to default to 100)
All Query Results are as expected when run against the old, non-partitioned collection
I have not yet tried to run the Queries from my Application

Here are my index settings:

{
"indexingMode": "consistent",
"automatic": true,
"includedPaths": [
    {
        "path": "/*",
        "indexes": [
            {
                "kind": "Range",
                "dataType": "Number",
                "precision": -1
            },
            {
                "kind": "Range",
                "dataType": "String",
                "precision": -1
            },
            {
                "kind": "Spatial",
                "dataType": "Point"
            }
        ]
    }
],
"excludedPaths": [
    {
        "path": "/_OLD/*"
    }
]
}

themightylc themightylc · Accepted Answer · 2019-01-24T09:20:09

I replicated the basic functionality of the Data Explorer from the Azure Portal locally within my application. Basically I'm just looping an SQL String through CreateDocumentQuery() and calling .ToString() on all objects in the resulting feed.

As I expected:

all queries show the correct and expected results
queries execute orders of magnitude faster than in the portal

This confirms my initial assumption, that the Data Explorer just sent me on a wild goose chase and in it's current state is not only a hassle to use, but virtually useless.

Inexplicable SQL Query Result in Cosmos DB Data Explorer

1 Answers