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

$exists:false prevents use of a sparse index on a different field

    XMLWordPrintableJSON

Details

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major - P3 Major - P3
    • 2.5.5
    • 2.4.0, 2.5.4
    • Querying
    • Linux devpocmdb01 3.5.0-17-generic #28-Ubuntu SMP Tue Oct 9 19:31:23 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux
    • Fully Compatible
    • Linux
    • Hide

      POC0:PRIMARY> db.foo.find()

      { "_id" : ObjectId("51c886922b4c67a8571a6720"), "a" : 1 } { "_id" : ObjectId("51c8869a2b4c67a8571a6721"), "a" : 1, "b" : true }

      POC0:PRIMARY> db.foo.ensureIndex(

      {a:1}

      ,

      {sparse:true}

      )
      POC0:PRIMARY> db.foo.find({a:1, b:{$exists:true}}).explain()
      {
      "cursor" : "BtreeCursor a_1",
      "isMultiKey" : false,
      "n" : 1,
      "nscannedObjects" : 2,
      "nscanned" : 2,
      "nscannedObjectsAllPlans" : 2,
      "nscannedAllPlans" : 2,
      "scanAndOrder" : false,
      "indexOnly" : false,
      "nYields" : 0,
      "nChunkSkips" : 0,
      "millis" : 0,
      "indexBounds" :

      { "a" : [ [ 1, 1 ] ] }

      ,
      "server" : "devpocmdb01:27017"
      }
      POC0:PRIMARY> db.foo.find({a:1, b:{$exists:false}}).explain()
      {
      "cursor" : "BasicCursor",
      "isMultiKey" : false,
      "n" : 1,
      "nscannedObjects" : 2,
      "nscanned" : 2,
      "nscannedObjectsAllPlans" : 2,
      "nscannedAllPlans" : 2,
      "scanAndOrder" : false,
      "indexOnly" : false,
      "nYields" : 0,
      "nChunkSkips" : 0,
      "millis" : 0,
      "indexBounds" : {

      },
      "server" : "devpocmdb01:27017"
      }

      Show
      POC0:PRIMARY> db.foo.find() { "_id" : ObjectId("51c886922b4c67a8571a6720"), "a" : 1 } { "_id" : ObjectId("51c8869a2b4c67a8571a6721"), "a" : 1, "b" : true } POC0:PRIMARY> db.foo.ensureIndex( {a:1} , {sparse:true} ) POC0:PRIMARY> db.foo.find({a:1, b:{$exists:true}}).explain() { "cursor" : "BtreeCursor a_1", "isMultiKey" : false, "n" : 1, "nscannedObjects" : 2, "nscanned" : 2, "nscannedObjectsAllPlans" : 2, "nscannedAllPlans" : 2, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 0, "indexBounds" : { "a" : [ [ 1, 1 ] ] } , "server" : "devpocmdb01:27017" } POC0:PRIMARY> db.foo.find({a:1, b:{$exists:false}}).explain() { "cursor" : "BasicCursor", "isMultiKey" : false, "n" : 1, "nscannedObjects" : 2, "nscanned" : 2, "nscannedObjectsAllPlans" : 2, "nscannedAllPlans" : 2, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 0, "indexBounds" : { }, "server" : "devpocmdb01:27017" }

    Description

      If you query on two fields, and the first field is sparsly indexed, querying for $exists:true on the second field will use the first field's index. However, querying for $exists:false on the second field will prevent the use of the FIRST field's index!

      The problem does not occur if the first field is fully (not sparsely) indexed.

      Attachments

        Activity

          People

            benety.goh@mongodb.com Benety Goh
            dwagner David Wagner
            Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: