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

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • 2.5.5
    • Affects Version/s: 2.4.0, 2.5.4
    • Component/s: Querying
    • Environment:
      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" }

      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.

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

              Created:
              Updated:
              Resolved: