Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-14176

natural order sort specification ignored if query is specified

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • 2.6.2, 2.7.2
    • Affects Version/s: 2.6.1
    • Component/s: Querying
    • Labels:
    • Fully Compatible
    • ALL

      Issue Status as of Jun 06, 2014

      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.


      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().


      db.coll.find( {x: "some value"} ).hint( {$natural: 1} ).sort( {$natural: -1} )

      This example sorts in ascending natural order (1) instead of descending (-1).

      Documents returned by such queries may not be in the sort order the user expects.

      In both cases, specifying only a .hint() and not a .sort() will work around the issue.

      Versions 2.6.0 and 2.6.1 are affected by this issue.

      The fix is included in the 2.6.2 production release.

      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() {
          x = 1
          for (var i=0; i<5; i++) {
              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)

            david.storch@mongodb.com David Storch
            bruce.lucas@mongodb.com Bruce Lucas (Inactive)
            0 Vote for this issue
            7 Start watching this issue