ISSUE SUMMARY
This ticket describes two issues with natural order sorts on indexed queries.
1. If a query over an indexed field is specified, the natural sort order is ignored.
Example:
Assume an index on {x: 1}.
db.coll.find( {x: "some value"} ).sort( {$natural: 1} )
This example ignores the natural sort order and instead returns results ordered according to the index scan.
2. If both a hint on $natural and a sort on $natural are specified, then the hint direction overrules the sort direction. This is a deviation from the 2.4 behavior, where .sort() overruled .hint().
Example:
db.coll.find( {x: "some value"} ).hint( {$natural: 1} ).sort( {$natural: -1} )
This example sorts in ascending natural order (1) instead of descending (-1).
USER IMPACT
Documents returned by such queries may not be in the sort order the user expects.
WORKAROUNDS
In both cases, specifying only a .hint() and not a .sort() will work around the issue.
AFFECTED VERSIONS
Versions 2.6.0 and 2.6.1 are affected by this issue.
FIX VERSION
The fix is included in the 2.6.2 production release.
RESOLUTION DETAILS
The query planner now obeys the $natural sort order and prefers .sort() to .hint() if both are specified. This restores the behavior of version 2.4.
Original description
Sorting in natural order in 2.6 has two problems:
(1) If a query over an indexed field is specified and no hint is provided, a BtreeCursor is used and the specified sort order is ignored.
(2) If a hint is provided, the sign of the hint parameter determines the sort order instead of the specified sort.
Following tests show the two issues (1) and (2):
- query parameters - ---------------- result ---------------------
2.4.10 2.6.1
query hint sort cursor sorted? cursor sorted?
non-empty none -1 ReverseCursor down BtreeCursor x_1 no <--- (1)
non-empty none 1 BasicCursor up BtreeCursor x_1 no <--- (1)
non-empty -1 -1 ReverseCursor down BasicCursor down
non-empty -1 1 BasicCursor up BasicCursor down <--- (2)
non-empty 1 -1 ReverseCursor down BasicCursor up <--- (2)
non-empty 1 1 BasicCursor up BasicCursor up
empty none -1 ReverseCursor down BasicCursor down
empty none 1 BasicCursor up BasicCursor up
empty -1 -1 ReverseCursor down BasicCursor down
empty -1 1 BasicCursor up BasicCursor down <--- (2)
empty 1 -1 ReverseCursor down BasicCursor up <--- (2)
empty 1 1 BasicCursor up BasicCursor up
- "non-empty" query means a query over an indexed field is specified; "empty" means no predicate is specified.
- specified hint was either none, .hint({$natural:1}), or .hint({$natural:-1})
- specified sort was either .sort({$natural:1}) or .sort({$natural:-1})
Above was generated by the following test:
function test(q, hint, sort, explain) {
c = db.c.find(q).sort({$natural: sort}).showDiskLoc()
if (hint) c = c.hint({$natural: hint})
if (explain)
return c.explain().cursor
else {
sum = 0
sum_abs = 0
last_off = null
c.forEach(function(d) {
off = d.$diskLoc.offset
if (last_off) {
sum += off - last_off
sum_abs += Math.abs(off - last_off)
}
last_off = off
})
return sum==sum_abs? "up" : sum==-sum_abs? "down" : "none"
}
}
function test_all() {
db.c.drop()
db.c.ensureIndex({x:1})
x = 1
for (var i=0; i<5; i++) {
db.c.insert({x:x})
x = -x
}
var empty = {};
var non_empty = {x:{$exists:1}};
[0, -1, 1].forEach(function(hint) {
[-1, 1].forEach(function(sort) {
[empty, non_empty].forEach(function(q) {
explained = test(q, hint, sort, true)
sorted = test(q, hint, sort, false)
print(q==empty?"empty":"non-empty", hint?hint:"none", sort, explained, sorted)
})
})
})
}
- is duplicated by
-
SERVER-14175 After creating an index in a capped collection, sorting it in reverse natural order doesn't work anymore
-
- Closed
-