Cosmos DB : cross partition query can not be directly served by the gateway

Question

Any idea why when I execute the query below in Cosmos DB using the rest api I get the error below? The same query without the order by works fine...

I have set the header "x-ms-documentdb-query-enablecrosspartition: True" in both cases and I am using PHP to make the requests.

MULTI PARTITION QUERY THAT WORKS:

SELECT c.id, c.name, c.age FROM c where c.age = 30

MULTI PARTITION QUERY WITH ERROR:

SELECT c.id, c.name, c.age FROM c where c.age = 30 order by c.age asc

ERROR:

Client error: POST https://yeapp-cosmosdb.documents.azure.com//dbs/-JJZAA==/colls/-JJZAL+WPKw=/docs

resulted in a 400 BadRequest response: {"code":"BadRequest","message":"The provided cross partition query can not be directly served by the gateway. This is a (truncated...)

Answer 1

I just had the same problem, which seems to me just recently manifested. I did not have it before. I have the same problem when I use SELECT DISTINCT on a query with joins...and here is why https://docs.microsoft.com/en-us/rest/api/cosmos-db/querying-cosmosdb-resources-using-the-rest-api#Queries-that-cannot-be-served-by-gateway

If you remove the ORDER BY, and in my case the DISTINCT, the query works...

This is unfortunate. Note: the same query works from the Azure Portal, but not from the REST API. The Azure portal however forces you to go through the paginated results and it is not really the way to go for long query results or automated tasks...

From the above link we can see this:

Any query that requires state across continuations cannot be served by the gateway. This includes: TOP ORDER BY OFFSET LIMIT Aggregates DISTINCT GROUP BY Queries that can be served by the gateway include: Simple projections Filters

Answer 2

I am having the same trouble making the request using an R script. The indexing policy for the DB is the default, which includes range and spatial indexes for all items. My query works as expected without an "ORDER BY" clause and even with an "ORDER BY" clause as long as there is only one result. Also, it works when my "WHERE" clause specifies a range, e.g. c._ts > 12345678910, so I do not think ngruson's suggestion is the problem.

According to this page, a 400 error can arise from a poorly-constructed JSON attachment.

Here is the JSON that works:

{\"query\":\"SELECT TOP 100 * FROM c WHERE c.id = \\\"F6OWIDUtl0PElDAg0BPDr-j\\\" ORDER BY c._ts DESC\",\"parameters\":[]}

and here is the JSON that returns an error:

{\"query\":\"SELECT TOP 100 * FROM c WHERE c.id != null ORDER BY c._ts DESC\",\"parameters\":[]}

I don't see an obvious difference, so I am assuming the problem lies somewhere else.

(I would have added this in the comments, but SO won't let me comment with my rep level, so it was either "answer" here or duplicate the question elsewhere.)