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://learn.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