How can we improve Azure Cosmos DB?

Mongo DB API - cursor.sort() does not return sorted result

When I use find() with sort(), the results are not sorted correctly. The order of the returned items do change, but in a strange order. (MongoDB returns correctly sorted result with the same query). Is this a CosmosDB bug?

Here are the queries and results. The queries are executed with a Mongo DB shell connected to a Cosmos DB instance. You can see that the header_timestamp order are incorrect.

> db.datastore.find({}, {_id: 1, header_timestamp: 1})

{ "_id" : "object-bbfg0pritvunrk0bi170", "header_timestamp" : NumberLong(1) }
{ "_id" : "object-bbfg0rjitvunrk0bi180", "header_timestamp" : NumberLong(3) }
{ "_id" : "object-bbfg0s3itvunrk0bi18g", "header_timestamp" : NumberLong(4) }
{ "_id" : "object-bbfg0qritvunrk0bi17g", "header_timestamp" : NumberLong(2) }
{ "_id" : "object-bbfg0tjitvunrk0bi190", "header_timestamp" : NumberLong(5) }

> db.datastore.find({}, {_id: 1, header_timestamp: 1}).sort({"header_timestamp": 1})

{ "_id" : "object-bbfg0pritvunrk0bi170", "header_timestamp" : NumberLong(1) }
{ "_id" : "object-bbfg0rjitvunrk0bi180", "header_timestamp" : NumberLong(3) }
{ "_id" : "object-bbfg0s3itvunrk0bi18g", "header_timestamp" : NumberLong(4) }
{ "_id" : "object-bbfg0qritvunrk0bi17g", "header_timestamp" : NumberLong(2) }
{ "_id" : "object-bbfg0tjitvunrk0bi190", "header_timestamp" : NumberLong(5) }

> db.datastore.find({}, {_id: 1, header_timestamp: 1}).sort({"header_timestamp": -1})
{ "_id" : "object-bbfg0rjitvunrk0bi180", "header_timestamp" : NumberLong(3) }
{ "_id" : "object-bbfg0pritvunrk0bi170", "header_timestamp" : NumberLong(1) }
{ "_id" : "object-bbfg0s3itvunrk0bi18g", "header_timestamp" : NumberLong(4) }
{ "_id" : "object-bbfg0qritvunrk0bi17g", "header_timestamp" : NumberLong(2) }
{ "_id" : "object-bbfg0tjitvunrk0bi190", "header_timestamp" : NumberLong(5) }

Results on a local Mongo DB instance:

> db.datastore.find({}, {_id: 1, header_timestamp: 1}).sort({"header_timestamp": 1})

{ "_id" : "object-bbfg0pritvunrk0bi170", "header_timestamp" : NumberLong(1) }
{ "_id" : "object-bbfg0qritvunrk0bi17g", "header_timestamp" : NumberLong(2) }
{ "_id" : "object-bbfg0rjitvunrk0bi180", "header_timestamp" : NumberLong(3) }
{ "_id" : "object-bbfg0s3itvunrk0bi18g", "header_timestamp" : NumberLong(4) }
{ "_id" : "object-bbfg0tjitvunrk0bi190", "header_timestamp" : NumberLong(5) }

> db.datastore.find({}, {_id: 1, header_timestamp: 1}).sort({"header_timestamp": -1})
{ "_id" : "object-bbfg0tjitvunrk0bi190", "header_timestamp" : NumberLong(5) }
{ "_id" : "object-bbfg0s3itvunrk0bi18g", "header_timestamp" : NumberLong(4) }
{ "_id" : "object-bbfg0rjitvunrk0bi180", "header_timestamp" : NumberLong(3) }
{ "_id" : "object-bbfg0qritvunrk0bi17g", "header_timestamp" : NumberLong(2) }
{ "_id" : "object-bbfg0pritvunrk0bi170", "header_timestamp" : NumberLong(1) }

(previously posted on https://social.msdn.microsoft.com/Forums/en-US/9c649d33-2093-4841-9e14-a6eb44ece7df/cursorsort-does-not-return-sorted-result?forum=azurecosmosdb)

96 votes
Vote
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
You have left! (?) (thinking…)
Hadinata shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

4 comments

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...
  • alex commented  ·   ·  Flag as inappropriate

    I second this.

    It seems like when paging through all of the results, Cosmos DB sorts the results but grouped by partition.

    However, if you then ask for a specific page that crosses partition boundaries, Cosmos DB refuses to cross boundaries. Instead, it just goes to the partition holding the first result and gives you the correct number of results but all from that partition. This results in duplicates and omissions when paging.

  • dave commented  ·   ·  Flag as inappropriate

    I agree.
    I ran into the error message "Cross partition query with TOP/ORDER BY or aggregate functions is not supported" even when the query is NOT cross partition.
    My query included a single shard key equals match in the Find()

  • Anonymous commented  ·   ·  Flag as inappropriate

    For a big business, we definitely have to use sharding in order to allow large RU.

    However it seems single sort doesn't work properly across a sharding collection.

    It affecting our business seriously. Please take it as a priority.

    Thanks

Feedback and Knowledge Base