[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 ) ) ) 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}) ) { "_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() } } ).explain() } 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() } ).explain() } 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 ] |
|
|
| Comment by Aaron Staple [ 29/Mar/11 ] |
|
The difference in behavior you describe between queries with an index and without is |