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

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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Fixed
    • Affects Version/s: 2.4.0, 2.5.4
    • Fix Version/s: 2.5.5
    • 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
    • Backwards Compatibility:
      Fully Compatible
    • Operating System:
      Linux
    • Steps To Reproduce:
      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

          Issue Links

            Activity

              People

              • Votes:
                1 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: