query optimizer fails to use index when sort spec is inconsistent with index spec

XMLWordPrintableJSON

    • Minor Change
    • ALL
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      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

        1. server11568.js
          0.2 kB
          Benety Goh
        2. server11568.js
          0.6 kB
          Benety Goh

            Assignee:
            Benety Goh
            Reporter:
            Hannes Magnusson (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: