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
    • None
    • Fully Compatible
    • ALL

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

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

              Created:
              Updated:
              Resolved: