Strange query results in Azure Cosmos DB

Question

I have following documents in my Azure Cosmos DB:

{
    "id": "token",
    "User": {
        "UserToken": "token",
        "Email": "test@email.com"
    },
    "_ts": 1541493290
}

When I run the following query:

SELECT * FROM root  
WHERE ((root["User"]["UserToken"] = "token")  
OR CONTAINS(root["User"]["Email"], "token"))  
ORDER BY root["_ts"] DESC

Nothing is returned. But when I change it a bit. For example byconverting Email to email:

SELECT * FROM root  
WHERE ((root["User"]["UserToken"] = "token")  
OR CONTAINS(root["User"]["email"], "token"))  
ORDER BY root["_ts"] DESC

The result is found. Moreover when I remove ORDER BY clause, also query returns me a result. So the query is like following

SELECT * FROM root  
WHERE ((root["User"]["UserToken"] = "token")  
OR CONTAINS(root["User"]["Email"], "token"))

Moreover, when I edit the document (like open it, add an empty line and save), some magic happens in the background and the document is found. For quite "new" documents (less than 1-3 months), I can search them without my "magic" trick.

Indexes definition is:

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

What I did wrong?

UPDATE the answer is not a full explanation but it helps a lot. Full explanation is in my blog (https://stapp.space/ridiculous-bug-in-azure-cosmos-db/)

user_7523974 · Answer

CONTAINS(root["User"]["Email"], "token") won't work if you have strings indexed as Hash. They need to be Range with -1 precision. Hash only works for equality checks.

That's why the lowercase one is working. Because it cannot find the property and it just ignores it, falling back to the equality check. The first one finds it, sees that it's not indexed as Range and it just fails to return.

Changing indexing to this, will work:

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

On a side note, the _ts field is not the best way to do ordering based on creation. It is a unix timestamp in seconds, so any documents created in the same second won't be properly ordered.

Strange query results in Azure Cosmos DB

1 Answers