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

Queries containing $or may miss results if multiple clauses use the same index

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • 2.6.2, 2.7.2
    • Affects Version/s: 2.6.1, 2.7.0
    • Component/s: Querying
    • Labels:
    • ALL

      Issue Status as of June 2, 2014

      Given a query with an $or operator where:

      1. one of the $or clauses includes a regex, $mod, $type, $elemMatch, $exists, geo, or range predicate with a non-simple type (like {a: {$gt: {b: 3}}}), and
      2. that predicate has an index which it can use,

      then the query may miss results from one of the $or clauses. For example:

      db.foo.ensureIndex({name : 1})
      db.foo.insert({_id : 1, name : "Alice"})
      db.foo.insert({_id : 2, name : "Bob"})
      db.foo.find({$or : [{name : /^(B|C)/}, {name : "Alice"}]})
      { "_id" : 2, "name" : "Bob" }

      Significant user impact: query results may be incomplete.

      One workaround is to use the $natural operator to force a collection scan, which may have a significant performance impact:

      db.foo.find({$or : [{name : /^(B|^C)/}, {name : "Alice"}]}).hint({$natural : 1})
      { "_id" : 1, "name" : "Alice" }
      { "_id" : 2, "name" : "Bob" }

      If query rewrite is possible, using the $in operator provides another workaround:

      db.foo.find({name : {$in : [/^(B|^C)/, "Alice"]}})
      { "_id" : 1, "name" : "Alice" }
      { "_id" : 2, "name" : "Bob" }

      MongoDB production releases 2.6.0 and 2.6.1 are affected by this issue.

      The fix is included in the 2.6.2 production release.

      The issue stems from an incorrect filter being affixed to FETCH and IXSCAN query solution nodes, which would filter the result set too narrowly. When an $or clause has inexact bounds, we must filter the results after performing the index scan. The filter should be an $or of all the predicates assigned to the corresponding index. The fix was to ensure that the filter includes all necessary predicates.

      Original description

      For certain types of query predicates that include the $or operator, the query planner can output a plan with a semantically incorrect query execution tree. Results may be omitted if this query plan is selected.

      Specifically, if an OR expression contains at least one clause that generates "exact" bounds on an index and at least one clause that generates "inexact" bounds on the same index, then the resulting filter constructed for the index scan may only include the "inexact" predicates. In this case, results unique to the "exact" clauses will not be returned.

      To reproduce:

      > db.foo.ensureIndex({name:1})
      	"createdCollectionAutomatically" : true,
      	"numIndexesBefore" : 1,
      	"numIndexesAfter" : 2,
      	"ok" : 1
      > db.foo.insert({_id:0, name:"thomas"})
      WriteResult({ "nInserted" : 1 })
      > db.foo.insert({_id:1, name:"alexandra"})
      WriteResult({ "nInserted" : 1 })
      > db.foo.find({$or:[{name:"thomas"}, {name:/^alexand(er|ra)/}]})
      { "_id" : 1, "name" : "alexandra" } // INCORRECT (results missing)
      > db.foo.find({$or:[{name:"thomas"}, {name:/^alexand(er|ra)/}]}).hint({$natural:1})
      { "_id" : 0, "name" : "thomas" }    // CORRECT
      { "_id" : 1, "name" : "alexandra" }

      Original report from mongodb-user: <https://groups.google.com/forum/#!topic/mongodb-user/psQan0UKWDc>.

            david.storch@mongodb.com David Storch
            rassi J Rassi
            2 Vote for this issue
            6 Start watching this issue