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

Bug when sorting on a date attribute which is hash indexed

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

      Create the sample collection with the hashed index an a date:

      use sample_collection
      db.sample_collection.insert({ key: 'value1', timestamp: new Date(2014,0,3) })
      db.sample_collection.insert({ key: 'value2', timestamp: new Date(2014,0,1) })
      db.sample_collection.insert({ key: 'value3', timestamp: new Date(2014,0,5) })
      db.sample_collection.insert({ key: 'value4', timestamp: new Date(2014,0,2) })
      db.sample_collection.insert({ key: 'value5', timestamp: new Date(2014,0,4) })
      

      Create the hashed index

      db.sample_collection.ensureIndex({ timestamp: "hashed" })
      
      { "_id" : ObjectId("53ac8730176132b326c61fa7"), "key" : "value1", "timestamp" : ISODate("2014-01-03T05:00:00Z") }
      { "_id" : ObjectId("53ac8731176132b326c61fab"), "key" : "value5", "timestamp" : ISODate("2014-01-04T05:00:00Z") }
      { "_id" : ObjectId("53ac8730176132b326c61fa9"), "key" : "value3", "timestamp" : ISODate("2014-01-05T05:00:00Z") }
      { "_id" : ObjectId("53ac8730176132b326c61faa"), "key" : "value4", "timestamp" : ISODate("2014-01-02T05:00:00Z") }
      { "_id" : ObjectId("53ac8730176132b326c61fa8"), "key" : "value2", "timestamp" : ISODate("2014-01-01T05:00:00Z") }
      

      Now just make a query with a sort by timestamp:

      db.sample_collection.find({}).sort({timestamp: 1 })
      

      You can even see which values are actually being used for the sorting if you project out the _id and just select the timestamp:

      db.sample_collection.find({}, { _id: 0, timestamp: 1 }).sort({ timestamp: 1 })
      
      { "timestamp" : NumberLong("-6917732993018730184") }
      { "timestamp" : NumberLong("-165404006621635642") }
      { "timestamp" : NumberLong("337081226167495347") }
      { "timestamp" : NumberLong("8104989996664184399") }
      { "timestamp" : NumberLong("9096888038977474173") }
      
      Show
      Create the sample collection with the hashed index an a date: use sample_collection db.sample_collection.insert({ key: 'value1' , timestamp: new Date(2014,0,3) }) db.sample_collection.insert({ key: 'value2' , timestamp: new Date(2014,0,1) }) db.sample_collection.insert({ key: 'value3' , timestamp: new Date(2014,0,5) }) db.sample_collection.insert({ key: 'value4' , timestamp: new Date(2014,0,2) }) db.sample_collection.insert({ key: 'value5' , timestamp: new Date(2014,0,4) }) Create the hashed index db.sample_collection.ensureIndex({ timestamp: "hashed" }) { "_id" : ObjectId("53ac8730176132b326c61fa7"), "key" : "value1", "timestamp" : ISODate("2014-01-03T05:00:00Z") } { "_id" : ObjectId("53ac8731176132b326c61fab"), "key" : "value5", "timestamp" : ISODate("2014-01-04T05:00:00Z") } { "_id" : ObjectId("53ac8730176132b326c61fa9"), "key" : "value3", "timestamp" : ISODate("2014-01-05T05:00:00Z") } { "_id" : ObjectId("53ac8730176132b326c61faa"), "key" : "value4", "timestamp" : ISODate("2014-01-02T05:00:00Z") } { "_id" : ObjectId("53ac8730176132b326c61fa8"), "key" : "value2", "timestamp" : ISODate("2014-01-01T05:00:00Z") } Now just make a query with a sort by timestamp : db.sample_collection.find({}).sort({timestamp: 1 }) You can even see which values are actually being used for the sorting if you project out the _id and just select the timestamp: db.sample_collection.find({}, { _id: 0, timestamp: 1 }).sort({ timestamp: 1 }) { "timestamp" : NumberLong("-6917732993018730184") } { "timestamp" : NumberLong("-165404006621635642") } { "timestamp" : NumberLong("337081226167495347") } { "timestamp" : NumberLong("8104989996664184399") } { "timestamp" : NumberLong("9096888038977474173") }

      The bug is pretty straight forward to explain an replicate.
      Basically if you have a collection with a date attribute and you have a hashed index for that attribute, when o try to make simple find query sorting by that attribute MongoDB just sorts using the hashed index instead of sorting by the date itself.

            Assignee:
            Unassigned Unassigned
            Reporter:
            jasoares João Soares
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: