[SERVER-14176] natural order sort specification ignored if query is specified Created: 05/Jun/14  Updated: 11/Mar/15  Resolved: 05/Jun/14

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.6.1
Fix Version/s: 2.6.2, 2.7.2

Type: Bug Priority: Major - P3
Reporter: Bruce Lucas (Inactive) Assignee: David Storch
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Duplicate
is duplicated by SERVER-14175 After creating an index in a capped c... Closed
Tested
Backwards Compatibility: Fully Compatible
Operating System: ALL
Backport Completed:
Participants:

 Description   
Issue Status as of Jun 06, 2014

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)
            })
        })
    })
}



 Comments   
Comment by Githook User [ 06/Jun/14 ]

Author:

{u'username': u'dstorch', u'name': u'David Storch', u'email': u'david.storch@10gen.com'}

Message: SERVER-14176 query planner should obey $natural sort

(cherry picked from commit 89383b224f061d306240d621745ba082a4ecb23b)
Branch: v2.6
https://github.com/mongodb/mongo/commit/f8294a13108b43feb7de435f045b5b363c29faac

Comment by Githook User [ 05/Jun/14 ]

Author:

{u'username': u'dstorch', u'name': u'David Storch', u'email': u'david.storch@10gen.com'}

Message: SERVER-14176 query planner should obey $natural sort
Branch: master
https://github.com/mongodb/mongo/commit/89383b224f061d306240d621745ba082a4ecb23b

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