Why does MongoDB's "$and" operator sometimes use a different plan vs. specifying the criteria inline?

Question

It seems to me that the following two queries should have exactly the same "explain" output:

Query 1:

{
    $and: [
        { $or: [
            { Foo: "123" },
            { Bar: "456" }
        ] },
        { Baz: { $in: ["abc", "def"] } }
    ]
}

Query 2:

{
    $or: [
        { Foo: "123" },
        { Bar: "456" }
    ],
    Baz: { $in: ["abc", "def"] } }
}

Note that I have indexes on { Foo: -1, Baz: -1 } and { Bar: -1, Baz: -1 }, so this is optimized for the $or operator. And in fact, in the version for Query 2, in the explain output, I see two clauses, both with appropriate index bounds, one for (Foo, Baz) and one for (Bar, Baz). MongoDB is doing exactly what it's supposed to.

But in the first version (Query 1), there are no clauses anymore. It gives me a BasicCursor with no index bounds specified.

What's the difference between these two queries? Why does Mongo seem to be able to optimize #2 but not #1?

Right now I'm testing these queries using MongoVue, so I have control over the JSON, but ultimately I'm going to be using the C# driver, and I'm pretty sure it will always emit the syntax in #1 and not #2, so it's important to find out what's going on...

Answer 1

This seems to be a bug of some kind in mongodb. What version are you using?

According to that bug report the issue is resolved in 2.5.3.

Until we move to the later versions (I am at 2.4.6) we will have to be careful with the $and operator.

I am going to try it in 2.6 as well.

UPDATE:

Indeed it is fixed in 2.6.3 that I am now.

> db.test.find()
{ "_id" : 1, "Fields" : { "K1" : 123, "K2" : 456 } }
{ "_id" : 2, "Fields" : { "K1" : 456, "K2" : 123 } }
> db.test.getIndexes()
[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "test.test"
    },
    {
        "v" : 1,
        "key" : {
            "Fields.K1" : 1
        },
        "name" : "Fields.K1_1",
        "ns" : "test.test"
    },
    {
        "v" : 1,
        "key" : {
            "Fields.K2" : 1
        },
        "name" : "Fields.K2_1",
        "ns" : "test.test"
    }
]

> db.test.find({"$and" : [{ "Fields.K1" : 123, "Fields.K2" : 456}]}).explain()
{
    "cursor" : "BtreeCursor Fields.K1_1",
    "isMultiKey" : false,
    "n" : 1,
    "nscannedObjects" : 1,
    "nscanned" : 1,
    "nscannedObjectsAllPlans" : 2,
    "nscannedAllPlans" : 4,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "Fields.K1" : [
            [
                123,
                123
            ]
        ]
    },
    "server" : "benihime:27017",
    "filterSet" : false
}
> db.test.find({ "Fields.K1" : 123, "Fields.K2" : 456}).explain()
{
    "cursor" : "BtreeCursor Fields.K1_1",
    "isMultiKey" : false,
    "n" : 1,
    "nscannedObjects" : 1,
    "nscanned" : 1,
    "nscannedObjectsAllPlans" : 2,
    "nscannedAllPlans" : 4,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "Fields.K1" : [
            [
                123,
                123
            ]
        ]
    },
    "server" : "benihime:27017",
    "filterSet" : false
}