[SERVER-2855] $or queries don't return results unless specifying sort, when an index is used Created: 28/Mar/11  Updated: 30/Mar/12  Resolved: 29/Mar/11

Status: Closed
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: 1.6.3, 1.8.0
Fix Version/s: None

Type: Bug Priority: Blocker - P1
Reporter: Vicente Mundim Assignee: Aaron Staple
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Tested with MongoDB 1.8.0 and 1.6.3, probably this bug occurs in other versions too


Operating System: ALL
Participants:

 Description   

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?



 Comments   
Comment by Vicente Mundim [ 29/Mar/11 ]

SERVER-2302 is way more important, since it make things like pagination with $or queries brake, since it returns 0 for count, but 'find' still return documents.

Comment by Aaron Staple [ 29/Mar/11 ]

The difference in behavior you describe between queries with an index and without is SERVER-2302.
The fact that an index on the $or fields is not used if there is a sort is SERVER-1205.

Generated at Thu Feb 08 03:01:23 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.