[SERVER-75113] Query result not correctly sorted when using unwind and facet with concurrently inserts Created: 21/Mar/23  Updated: 17/Nov/23  Resolved: 17/Nov/23

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Micael Malta Assignee: Backlog - Query Execution
Resolution: Works as Designed Votes: 0
Labels: query-director-triage
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query Execution
Operating System: ALL
Steps To Reproduce:

https://github.com/micaelmalta/poc_mongo_facet_pagination

 

Participants:

 Description   

Query result are not correctly sorted when using unwind and facet when items are inserted concurrently

 

 .aggregate(   [   {"$match": {"_id": feed_id}},   {   "$lookup": {   "from": "items",   "localField": "_id",   "foreignField": "feed_id",   "as": "items",   }   },   {"$unwind": "$items"},   {   "$sort": {   "_id": 1,   },   },   {   "$facet": {   "result": [   {"$replaceRoot": {"newRoot": "$items"}},   {"$skip": offset},   {"$limit": limit},   ],   "total": [   {"$count": "count"},   ],   },   },   ],   ) 

This result on a random order instead of ordering by items._id

If inserts are done sequentially, results are as expected

Environment:

Any MongoDB version

 



 Comments   
Comment by Bernard Gorman [ 17/Nov/23 ]

The aggregation pipeline shown above is not sorting by "items._id", but by _id. The output of the $lookup-$unwind section of the pipeline immediately before the $sort is of the following form, for an illustrative value of feed_id=2 in the initial $match filter:

[
  { _id: 2, items: { _id: 1, feed_id: 2 } },
  { _id: 2, items: { _id: 2, feed_id: 2 } },
  { _id: 2, items: { _id: 3, feed_id: 2 } }
]

That is, the _id of each document is the _id from the original collection, not the foreign collection The pipeline is therefore sorting a set of documents which all have the same value, which means that they may sort in any order (every ordering is equally correct). If the pipeline is changed to sort on "items._id" instead, the results should be in the expected order.

Generated at Thu Feb 08 06:29:21 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.