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

XMLWordPrintableJSON

    • Type: Bug
    • Resolution: Done
    • Priority: 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
    • None
    • 3
    • None
    • None
    • None
    • None
    • None
    • None

      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:
            J Rassi (Inactive)
            Reporter:
            Andrew Zammit
            Votes:
            0 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:
              Resolved: