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

Array Compound Index Changes Sort Behavior

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 2.6.1
    • Component/s: Index Maintenance, Querying
    • Labels:
      None
    • ALL
    • Hide

      Consider the collection "things" for demonstration purposes, with 3 documents:

      db.things.insert({"_id" : 'a', arr: [{round:1, points:2000}, {round:2, points:1000}, {round:3, points:1000}]});
      db.things.insert({"_id" : 'b', arr: [{round:1, points:1500}, {round:2, points:1500}, {round:3, points:3000}]});
      db.things.insert({"_id" : 'c', arr: [{round:1, points:1000}, {round:2, points:2000}, {round:3, points:2000}]});
      

      The query:

      db.things.find({"arr.round" : 1}).sort({"arr.points" : -1 })
      

      returns the 3 documents in the following order:
      b, a, c

      However, if this index is created

      db.things.createIndex({"arr.round" : 1, "arr.points" : -1});
      

      The same query returns the documents in a different order (if index is used):
      a, b, c

      Nevertheless, If we want to always replicate this (second) sort behaviour with or without index, it can be done like this:

      db.things.find({"arr":{"$elemMatch" : {round : 1}}}).sort({"arr.$.points" : -1 })
      
      Show
      Consider the collection "things" for demonstration purposes, with 3 documents: db.things.insert({ "_id" : 'a' , arr: [{round:1, points:2000}, {round:2, points:1000}, {round:3, points:1000}]}); db.things.insert({ "_id" : 'b' , arr: [{round:1, points:1500}, {round:2, points:1500}, {round:3, points:3000}]}); db.things.insert({ "_id" : 'c' , arr: [{round:1, points:1000}, {round:2, points:2000}, {round:3, points:2000}]}); The query: db.things.find({ "arr.round" : 1}).sort({ "arr.points" : -1 }) returns the 3 documents in the following order: b, a, c However, if this index is created db.things.createIndex({ "arr.round" : 1, "arr.points" : -1}); The same query returns the documents in a different order (if index is used): a, b, c Nevertheless, If we want to always replicate this (second) sort behaviour with or without index, it can be done like this: db.things.find({ "arr" :{ "$elemMatch" : {round : 1}}}).sort({ "arr.$.points" : -1 })

      Creating a compound Index on the fields of embedded documents inside an array can change the sort behaviour if the index is used.

      This was detected on mongo version 2.6.1.

            Assignee:
            ramon.fernandez@mongodb.com Ramon Fernandez Marina
            Reporter:
            joaofranca João França
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: