[SERVER-57613] Sort + limit returns data in random order if sorted value is not unique Created: 10/Jun/21  Updated: 10/Jun/21  Resolved: 10/Jun/21

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

Type: Bug Priority: Major - P3
Reporter: Dovydas Kukalis Assignee: Edwin Zhou
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-51498 Sorting on duplicate values causes re... Closed
Operating System: ALL
Steps To Reproduce:

For the limit issue:

  1. Add this data to collection:

 

[{
    "_id" : ObjectId("60c22dcb34ab086d07102cbb"),
    "title" : "Test 1",
    "sort" : 1
}
{
    "_id" : ObjectId("60c22ddb34ab086d07102cd5"),
    "title" : "Test 2",
    "sort" : 1
},
{
    "_id" : ObjectId("60c22de634ab086d07102d06"),
    "title" : "Test 3",
    "sort" : 2
},
{
    "_id" : ObjectId("60c22e6a34ab086d07102e06"),
    "title" : "Test 4",
    "sort" : 1
},
{
    "_id" : ObjectId("60c22f2e34ab086d07102f53"),
    "title" : "Test 5",
    "sort" : 1
}]

2. Execute 

db.getCollection('test').find().limit(2).sort({ sort: 1 })

The result is:

 

[{
    "_id" : ObjectId("60c22dcb34ab086d07102cbb"),
    "title" : "Test 1",
    "sort" : 1
},
{
    "_id" : ObjectId("60c22ddb34ab086d07102cd5"),
    "title" : "Test 2",
    "sort" : 1
}]

3. Execute 

db.getCollection('test').find().limit(3).sort({ sort: 1 })

The result is (order changed):

 

[{
    "_id" : ObjectId("60c22e6a34ab086d07102e06"),
    "title" : "Test 4",
    "sort" : 1
},
{
    "_id" : ObjectId("60c22dcb34ab086d07102cbb"),
    "title" : "Test 1",
    "sort" : 1
},
{
    "_id" : ObjectId("60c22ddb34ab086d07102cd5"),
    "title" : "Test 2",
    "sort" : 1
}]

4. Execute 

db.getCollection('test').find().limit(4).sort({ sort: 1 })

The result is (completely changed again):

 

[{
    "_id" : ObjectId("60c22dcb34ab086d07102cbb"),
    "title" : "Test 1",
    "sort" : 1
},
{
    "_id" : ObjectId("60c22f2e34ab086d07102f53"),
    "title" : "Test 5",
    "sort" : 1
},
{
    "_id" : ObjectId("60c22ddb34ab086d07102cd5"),
    "title" : "Test 2",
    "sort" : 1
}, {
    "_id" : ObjectId("60c22e6a34ab086d07102e06"),
    "title" : "Test 4",
    "sort" : 1
}]

 

 

 

 

For the skip issue which is the result of the limit issue (I would think so):

  1. Add this data to collection:

[{
    "_id" : ObjectId("60c22dcb34ab086d07102cbb"),
    "title" : "Test 1",
    "sort" : 1
}
{
    "_id" : ObjectId("60c22ddb34ab086d07102cd5"),
    "title" : "Test 2",
    "sort" : 1
},
{
    "_id" : ObjectId("60c22de634ab086d07102d06"),
    "title" : "Test 3",
    "sort" : 2
},
{
    "_id" : ObjectId("60c22e6a34ab086d07102e06"),
    "title" : "Test 4",
    "sort" : 1
},
{
    "_id" : ObjectId("60c22f2e34ab086d07102f53"),
    "title" : "Test 5",
    "sort" : 1
}]

2. Execute 

db.getCollection('test').find({}).skip(0).limit(2).sort({ sort: 1 })

The result is:

 

[{
    "_id" : ObjectId("60c22dcb34ab086d07102cbb"),
    "title" : "Test 1",
    "sort" : 1
},
{
    "_id" : ObjectId("60c22ddb34ab086d07102cd5"),
    "title" : "Test 2",
    "sort" : 1
}]

3. Execute 

db.getCollection('test').find().skip(2).limit(2).sort({ sort: 1 })

The result is:

 

[{
    "_id" : ObjectId("60c22ddb34ab086d07102cd5"),
    "title" : "Test 2",
    "sort" : 1
},
{
    "_id" : ObjectId("60c22e6a34ab086d07102e06"),
    "title" : "Test 4",
    "sort" : 1
}]

Expected result is:

 

[{
    "_id" : ObjectId("60c22e6a34ab086d07102e06"),
    "title" : "Test 4",
    "sort" : 1
},
{
    "_id" : ObjectId("60c22f2e34ab086d07102f53"),
    "title" : "Test 5",
    "sort" : 1
}]

 

 

 

 

 

 

Participants:

 Description   

When sorted value is not unique, using limit returns data in random order which also affects skip functionality.



 Comments   
Comment by Edwin Zhou [ 10/Jun/21 ]

Hi dovydas@plutio.com,

Thanks for your detailed report and steps for reproduction. This issues appears to be a duplicate of SERVER-51498, which describes that the sorting behavior is unstable, and we shouldn't expect that the same results when running a sort are guaranteed. Here's our MongoDB documentation which describes sort consistency.

Best,
Edwin

Generated at Thu Feb 08 05:42:19 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.