Unable to query state with sorting when using CosmosDB

Expected Behavior

When using CosmosDB as the state store I would expect to be able to make a query with sorting.

{
    "sort": [{ "key": "createdTime", "order": "DESC" }]
}

Actual Behavior

When using a sort query CosmosDB errors:

{
  "code": "BadRequest",
  "message": "The provided cross partition query can not be directly served by the gateway. This is a first chance (internal) exception that all newer clients will know how to handle gracefully. This exception is traced, but unless you see it bubble up as an exception (which only happens on older SDK clients), then you can safely ignore this message.\r\nActivityId: 4b8d262e-7e15-4c91-886d-21599fec2ad8, Microsoft.Azure.Documents.Common/2.14.0",
  "additionalErrorInfo": {
    "partitionedQueryExecutionInfoVersion": 2,
    "queryInfo": {
      "distinctType": "None",
      "top": null,
      "offset": null,
      "limit": null,
      "orderBy": ["Descending"],
      "orderByExpressions": ["c[\"value\"][\"createdTime\"]"],
      "groupByExpressions": [],
      "groupByAliases": [],
      "aggregates": [],
      "groupByAliasToAggregateType": {},
      "rewrittenQuery": "SELECT c._rid, [{\"item\": c[\"value\"][\"createdTime\"]}] AS orderByItems, c AS payload\nFROM c\nWHERE ({documentdb-formattableorderbyquery-filter})\nORDER BY c[\"value\"][\"createdTime\"] DESC",
      "hasSelectValue": false,
      "dCountInfo": null
    },
    "queryRanges": [
      {
        "min": "",
        "max": "FF",
        "isMinInclusive": true,
        "isMaxInclusive": false
      }
    ]
  }
}

This might be a CosmosDB limitation? https://learn.microsoft.com/en-us/rest/api/cosmos-db/querying-cosmosdb-resources-using-the-rest-api#queries-that-cannot-be-served-by-gateway

TOP is listed on there but I can see that it's being handled by the sdk rather than CosmosDB which would be why that works.

for _, item := range queryResponse.Items {
    tempItem := CosmosItem{}
    err := json.Unmarshal(item, &tempItem)
    if err != nil {
        return nil, "", err
    }
    if (resultLimit != 0) && (len(items) >= resultLimit) {
        break
    }
    items = append(items, tempItem)
}

Ordering though still tries to use the CosmosDB client:

if sz := len(qq.Sort); sz != 0 {
  order := make([]string, sz)
  for i, item := range qq.Sort {
      if item.Order == query.DESC {
          order[i] = replaceKeywords("c.value."+item.Key) + " DESC"
      } else {
          order[i] = replaceKeywords("c.value."+item.Key) + " ASC"
      }
  }
  orderBy = " ORDER BY " + strings.Join(order, ", ")
}

Steps to Reproduce the Problem

Use any sort query when using CosmosDB as a state store.

Query API is Alpha and will not be made stable.

Because Dapr by default manages the partition key of entities written we only support Cross Partition Queries. And Cross Partition queries are inherently very limited. The article you linked to contains those limitations.

The only way to implement Order By would be to read the entirety of the collection into memory and then sort. That's not a good idea however, so it's simply not supported.

We are considering implementing a new DocumentStore building block in the future which would support rich query capabilities. CosmosDB would receive a first class implementation at that time - this will be a completely separate implementation from the current Dapr Query API which will be deprecated at that time. That building block is also not intended to be compatible with any data written via the state store component - though it may be possible to query that data.

dapr / components-contrib