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

"Whole index scan" query solutions can use incompatible indexes, return incorrect results

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • 2.6.2, 2.7.1
    • Affects Version/s: 2.6.1, 2.7.0
    • Component/s: Querying
    • None
    • Environment:
    • Hide
      use test;
      db.sorthashed.insert([
      	{str:'bravo'},
      	{str:'alpha'},
      	{str:'tango'},
      	{str:'charlie'}
      ]);
      db.sorthashed.ensureIndex({str:'hashed'},{background:false});
      db.sorthashed.find({}).sort({str:1}).pretty();
      
      Show
      use test; db.sorthashed.insert([ {str: 'bravo' }, {str: 'alpha' }, {str: 'tango' }, {str: 'charlie' } ]); db.sorthashed.ensureIndex({str: 'hashed' },{background: false }); db.sorthashed.find({}).sort({str:1}).pretty();
    • Server 2.7.1

      Issue Status as of May 16, 2014

      ISSUE SUMMARY
      When the query planner is picking an index for a whole index scan solution (to provide a sort), it considers the index's sparseness flag but doesn't consider whether or not the index is of a special index type. Thus the wrong index may be used and results won't be what the user expects. This is a regression from 2.4.

      USER IMPACT
      Incorrectly using a hashed index for a sort will generate results out of order. Incorrectly using a text or geo index for a sort will similarly generate results out of order, and additionally can cause results to be missing from the result set.

      WORKAROUNDS
      On a query-by-query basis, users may work around this issue by employing the hint() feature or index filter feature to force the query planner to choose a non-offending index. There is no workaround that can be employed server-wide.

      AFFECTED VERSIONS
      MongoDB production 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
      Use indexes of a special index type to provide a sort only when the query predicate can be used to guarantee the sort order and that all documents to be returned are indexed.

      Original description

      If a particular field is defined as a hashed index, when sorting the result set is not in the expected order. When using a non-hashed (normal) index or no index at all on the specific String field, the result set's order is correct when sorting. This can be reproduced in the MongoDB shell, Node.js driver, and PHP driver, so I'm assuming this is a problem with the core server.

      The steps to reproduce show only how to reproduce the problem, but the following code illustrates the effect:

      use test;
      
      db.sorthashed.insert([
      	{str:'bravo'},
      	{str:'alpha'},
      	{str:'tango'},
      	{str:'charlie'}
      ]); // intentionally inserted out of alpha order, field in question is `str`
      db.sorthashed.find({}).sort({str:1}).pretty(); // shows in order
      
      db.sorthashed.ensureIndex({str:'hashed'},{background:false});
      db.sorthashed.find({}).sort({str:1}).pretty(); // shows out of order
      
      db.sorthashed.dropIndex('str_hashed');
      db.sorthashed.find({}).sort({str:1}).pretty(); // shows in order
      
      db.sorthashed.ensureIndex({str:-1},{background:false}); // using desc index order as an example
      db.sorthashed.find({}).sort({str:1}).pretty(); // shows in order
      

            Assignee:
            rassi J Rassi
            Reporter:
            zammit.andrew@gmail.com Andrew Zammit
            Votes:
            0 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:
              Resolved: