Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-57613

Sort + limit returns data in random order if sorted value is not unique

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 4.4.4
    • Component/s: None
    • None
    • ALL
    • Hide

      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
}]

       

       

       

       

       

       

      Show
      For the limit issue: 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): 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 }]            

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

            Assignee:
            edwin.zhou@mongodb.com Edwin Zhou
            Reporter:
            dovydas@plutio.com Dovydas Kukalis
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved: