Because the index is multikey, we cannot constrain the index on both upper and lower bounds. Instead, only one of the bounds is chosen. This is so we can return correct matches in cases like the following:

save

{a:[1,10]}

query {a:{$gt:2,$lt:9}}

The document should be returned because both query criteria ($gt and $lt) match a value in the a array, but if we were to use both upper and lower index bounds we would not find this document using the index.

Right now if you want to do an index scan with both upper and lower bounds, you need to use a non multi key index.

At some point we may implement tighter index bounds for the related elemMatch query, potentially as part of SERVER-1264.

Aaron Staple (Inactive)
added a comment - Oct 27 2011 06:34:31 AM +00:00 The behavior described is as designed, and is new in 2.0.
Because the index is multikey, we cannot constrain the index on both upper and lower bounds. Instead, only one of the bounds is chosen. This is so we can return correct matches in cases like the following:
save
{a:[1,10]}
query {a:{$gt:2,$lt:9}}
The document should be returned because both query criteria ($gt and $lt) match a value in the a array, but if we were to use both upper and lower index bounds we would not find this document using the index.
Right now if you want to do an index scan with both upper and lower bounds, you need to use a non multi key index.
At some point we may implement tighter index bounds for the related elemMatch query, potentially as part of SERVER-1264 .
There is some additional discussion here: SERVER-958

Kyle Banker
added a comment - Mar 29 2012 07:33:06 PM +00:00 I think that we should reconsider this. Have a look at this issue:
https://groups.google.com/forum/?fromgroups#!topic/mongodb-user/3xfoqFW6uIA
The user make a good point:
What we want is that any one key matches BOTH criteria, not any key matches either.
In the example:
save
{a:[1,10]}
query {a:{$gt:2,$lt:9}}
I don't believe that the document should be returned. For example, what about this:
save (
{a: 1}
)
query {a:{$gt:2,$lt:9}}
In this case, both ranges are taken into account.
This may be a case where we need a new operator to account for both semantics, but I believe that both are reasonable. Arguably, users can get the current, v2.0 semantic like this:
query {$or: [
{a:$gt:2}
, {a: {$lt:9}}]}

Eliot Horowitz
added a comment - Mar 30 2012 03:27:20 AM +00:00 The semantics wanted there are what $elemMatch are for.
I don't think changing the semantics of a query operator is a great idea at this point.