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

Array Compound Index Changes Sort Behavior

    XMLWordPrintableJSON

Details

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major - P3 Major - P3
    • None
    • 2.6.1
    • Index Maintenance, Querying
    • 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 })

    Description

      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.

      Attachments

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved: