When doing $or queries, when indexes are used without specifying sort clause, no results are returned. This happens, for example, when doing counts, which ignores sort clauses. Below I've created a small example that reproduce the bug:

First I've created a new database, and put some documents there:

> use videos_test
switched to db videos_test
> db.videos.save(

{tags: ['a', 'b', 'c'], 'title': 'video1'}

)
> db.videos.save(

{tags: ['b', 'c', 'd'], 'title': 'video2'}

)
> db.videos.save(

{tags: ['c', 'd', 'e'], 'title': 'video3'}

)

Now I perform an $or query, searching by tags in the $or clause and outside it:

> db.videos.find({tags: {$all: ['b']}, $or: [{tags: {$all: ['a']}}]})

{ "_id" : ObjectId("4d9103d1d242d7fd761ae568"), "tags" : [ "a", "b", "c" ], "title" : "video1" }

> db.videos.find({tags: {$all: ['b']}, $or: [{tags: {$all: ['d']}}]})

{ "_id" : ObjectId("4d9103e0d242d7fd761ae569"), "tags" : [ "b", "c", "d" ], "title" : "video2" }

> db.videos.find({tags: {$all: ['b']}, $or: [{tags: {$all: ['a']}}, {tags: {$all: ['d']}}]})

{ "_id" : ObjectId("4d9103d1d242d7fd761ae568"), "tags" : [ "a", "b", "c" ], "title" : "video1" } { "_id" : ObjectId("4d9103e0d242d7fd761ae569"), "tags" : [ "b", "c", "d" ], "title" : "video2" }

Everything is fine here, the results are as expected. Now I create an index on tags:

> db.videos.ensureIndex(

{tags: 1}

)
> db.videos.find({tags: {$all: ['b']}, $or: [{tags: {$all: ['a']}}, {tags: {$all: ['d']}}]})
> db.videos.find({tags: {$all: ['b']}, $or: [{tags: {$all: ['a']}}, {tags: {$all: ['d']}}]}).sort(

{title: -1}

)

{ "_id" : ObjectId("4d9103e0d242d7fd761ae569"), "tags" : [ "b", "c", "d" ], "title" : "video2" } { "_id" : ObjectId("4d9103d1d242d7fd761ae568"), "tags" : [ "a", "b", "c" ], "title" : "video1" }

The first $or query without sort doesn't return any documents, but the second one, with sort clause returns correctly. I asked the server to explain the queries:

> db.videos.find({tags: {$all: ['b']}, $or: [{tags: {$all: ['a']}}, {tags: {$all: ['d']}}]}).explain()
{
"clauses" : [
{
"cursor" : "BasicCursor",
"nscanned" : 0,
"nscannedObjects" : 0,
"n" : 0,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {

}
},
{
"cursor" : "BasicCursor",
"nscanned" : 0,
"nscannedObjects" : 0,
"n" : 0,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {

}
}
],
"nscanned" : 0,
"nscannedObjects" : 0,
"n" : 0,
"millis" : 0
}
> db.videos.find({tags: {$all: ['b']}, $or: [{tags: {$all: ['a']}}, {tags: {$all: ['d']}}]}).sort(

{title: -1}

).explain()
{
"cursor" : "BtreeCursor tags_1",
"nscanned" : 2,
"nscannedObjects" : 2,
"n" : 2,
"scanAndOrder" : true,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : true,
"indexOnly" : false,
"indexBounds" :

{ "tags" : [ [ "b", "b" ] ] }

}

The one without sort clause uses two clauses, both of them don't use indexes, reporting 0 scanned objects. The one with sort clause uses an index, and reports 2 scanned objects, which is correct.

Finally, I've attempt to "merge", the outside tag filter into the $or query:

> db.videos.find({$or: [{tags: {$all: ['b', 'a']}}, {tags: {$all: ['b', 'd']}}]})

{ "_id" : ObjectId("4d9103d1d242d7fd761ae568"), "tags" : [ "a", "b", "c" ], "title" : "video1" } { "_id" : ObjectId("4d9103e0d242d7fd761ae569"), "tags" : [ "b", "c", "d" ], "title" : "video2" }

> db.videos.find({$or: [{tags: {$all: ['b', 'a']}}, {tags: {$all: ['b', 'd']}}]}).sort(

{title: -1}

)

{ "_id" : ObjectId("4d9103e0d242d7fd761ae569"), "tags" : [ "b", "c", "d" ], "title" : "video2" } { "_id" : ObjectId("4d9103d1d242d7fd761ae568"), "tags" : [ "a", "b", "c" ], "title" : "video1" }

It returns correctly even without sort clause. Explaining the queries gives this:

> db.videos.find({$or: [{tags: {$all: ['b', 'a']}}, {tags: {$all: ['b', 'd']}}]}).explain()
{
"cursor" : "BtreeCursor tags_1",
"nscanned" : 2,
"nscannedObjects" : 2,
"n" : 2,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : true,
"indexOnly" : false,
"indexBounds" :

{ "tags" : [ [ "b", "b" ] ] }

}
> db.videos.find({$or: [{tags: {$all: ['b', 'a']}}, {tags: {$all: ['b', 'd']}}]}).sort(

{title: -1}

).explain()
{
"cursor" : "BasicCursor",
"nscanned" : 3,
"nscannedObjects" : 3,
"n" : 2,
"scanAndOrder" : true,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {

}
}

Now it uses an index when sort clause is not specified, but it doesn't use the index when sort is specified. Maybe the query planner chose not to use index, but I really don't know why.

Is this an expected behavior for $or queries?