[SERVER-33107] Incorrect result while using pipeline with skip and limit Created: 03/Feb/18  Updated: 28/Feb/18  Resolved: 05/Feb/18

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

Type: Bug Priority: Major - P3
Reporter: Yaroslav Assignee: Mark Agarunov
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

db.version() --> 3.6.2
OS: MacOS High Sierra 10.13.1


Attachments: File test.json    
Issue Links:
Duplicate
duplicates SERVER-28195 $skip followed by $limit in aggregati... Closed
Operating System: ALL
Steps To Reproduce:

STR:
1. Create new test collection
2. Insert documents from the test.json file
3. Run the following query:

 
db.getCollection('test').aggregate([
    {
        "$match": {
            "$and": [ { "status": { "$eq": "active" } } ]
        }
    },
    { "$sort": { "status": 1 } },
    { "$skip": 4 },
    { "$limit": 2 }
])

Expected result: the documents with ids 6 and 7 are returned as in chained query

 
db.getCollection('test')
    .find({
            "$and": [ { "status": { "$eq": "active" } } ]
    })
    .sort({ "status": 1 })
    .skip(4)
    .limit(2)

Actual result: the documents with ids 3 and 5 are returned

Participants:

 Description   

I was trying to use an aggregation pipeline in the following order:
match -> sort -> skip(4) -> limit(2)

In my case:
1. Match step returned 6 documents
2. Sort step sorts data, but they all have the same value in the sorted column
3. Skip step skips first 4 documents
4. Limit step limits the result to 2 documents.

I expected to see last 2 documents from the match result, but the query returned 2-nd and 4-th documents from the match result. If I set a limit to 3, then everything works as expected (I receive last 2 documents). The other way to get a correct result is to add an _id : 1 as the last sort column in sort step or exclude sort step at all.

If I use simple chaining like .find().sort().skip().limit() it works correct too.



 Comments   
Comment by Mark Agarunov [ 05/Feb/18 ]

Hello iamyardem,

Thank you for the report. Looking over the behavior described, I believe this expected behavior as described in SERVER-28195 which details the same issue. As you noted, to guarantee a stable sort order, you must sort by a field with differing values, such as _id.

Thanks,
Mark

Generated at Thu Feb 08 04:32:18 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.