[SERVER-11568] query optimizer fails to use index when sort spec is inconsistent with index spec Created: 05/Nov/13  Updated: 27/Oct/15  Resolved: 27/Nov/13

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.5.4
Fix Version/s: 2.5.5

Type: Bug Priority: Major - P3
Reporter: Hannes Magnusson Assignee: Benety Goh
Resolution: Done Votes: 0
Labels: 26qa, query_triage
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File server11568.js     File server11568.js    
Issue Links:
Depends
is depended on by DOCS-2319 Query Sort Order Validation Closed
Related
is related to SERVER-8067 incorrect use of sparse index to sort... Closed
is related to SERVER-11064 Stricter validation of index key patt... Closed
is related to SERVER-15287 Query planner sort analysis incorrect... Closed
Backwards Compatibility: Minor Change
Operating System: ALL
Participants:

 Description   

attached smoke test specifies index using the more correct numerical value

{a: 1}

instead of using boolean. still get similar results. When the sort spec is not exactly the same as the index spec, the query optimizer fails to come up with a plan that uses the sparse index. query planning output below:

Beginning planning, options = INCLUDE_COLLSCAN 
Canonical query:
ns=test.server11568 limit=0 skip=0
Tree: $and
Sort: { a: true }
Proj: {}
 
=============================
idx 0 is { _id: 1 }
idx 1 is { a: 1.0 } sparse
Finding relevant indices
rated tree
$and
 
Planner: outputted 0 indexed solutions.
Planner: outputting a collscan:
SORT
---pattern = { a: true }
---fetched = 1
---sortedByDiskLoc = 0
---getSort = []
Child:
------COLLSCAN
---------ns = test.server11568
--------- filter = $and
---------fetched = 1
---------sortedByDiskLoc = 0
---------getSort = []
 
not caching runner but returning 2 results

-------------------

2.4.6 (and afaict, since "forever")

> db.example.ensureIndex({title: true}, {sparse: true})
> db.example.insert({name: "Bob", title: "doctor"})
> db.example.insert({name: "Jim"})
> db.example.find().sort({title: true})
{ "_id" : ObjectId("5278370b13b0e338b3511f67"), "name" : "Bob", "title" : "doctor" }
> db.example.find().sort({title: 1})
{ "_id" : ObjectId("5278370b13b0e338b3511f67"), "name" : "Bob", "title" : "doctor" }
>

in master from today (worked fine ~7days ago):

 db.example.ensureIndex({title: true}, {sparse: true})
> db.example.insert({name: "Bob", title: "doctor"})
> db.example.insert({name: "Jim"})
> db.example.find().sort({title: true})
{ "_id" : ObjectId("527836b596870941233e3da8"), "name" : "Bob", "title" : "doctor" }
> db.example.find().sort({title: 1})
{ "_id" : ObjectId("527836bb96870941233e3da9"), "name" : "Jim" }
{ "_id" : ObjectId("527836b596870941233e3da8"), "name" : "Bob", "title" : "doctor" }
>

Suddenly it matters if its 1 or true



 Comments   
Comment by Benety Goh [ 27/Nov/13 ]

sort order will be validated and must be one of:

  • +1
  • -1
  • {$meta: "text"}

See DOCS-2319

Comment by Githook User [ 27/Nov/13 ]

Author:

{u'username': u'benety', u'name': u'Benety Goh', u'email': u'benety@mongodb.com'}

Message: SERVER-11568 normalize sort order in query and index key pattern during planning
Branch: master
https://github.com/mongodb/mongo/commit/b2a7d6099e8a0a937e8dd9cbed88b65b42676e97

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