Results in incorrect order for complex $or query with bounded sort on multi-key field when SORT plan used

XMLWordPrintableJSON

    • Type: Bug
    • Resolution: Done
    • Priority: Major - P3
    • None
    • Affects Version/s: None
    • Component/s: Querying
    • None
    • Query
    • ALL
    • None
    • 3
    • None
    • None
    • None
    • None
    • None
    • None

      The SortStageKeyGenerator component of the SORT query stage does not properly extract bounds from queries if the bounds generation planning process generates an OR plan. As a result, certain $or queries that use the SORT stage which are performing a bounded sort on a multi-key field can generate results in incorrect order.

      To reproduce:

      > db.bar.drop()
      true
      > db.bar.insert({_id: 0, a: [8], b: 1})
      WriteResult({ "nInserted" : 1 })
      > db.bar.insert({_id: 1, a: [6, 10], b: 1})
      WriteResult({ "nInserted" : 1 })
      > db.bar.find({$or: [{a: {$gt: 7}}, {a: {$gt: 6}}]}).sort({a: 1}) // Bounds generation planning generates FETCH <= IXSCAN plan: correct order.
      { "_id" : 0, "a" : [ 8 ], "b" : 1 }
      { "_id" : 1, "a" : [ 6, 10 ], "b" : 1 }
      > db.bar.find({$or: [{a: {$gt: 7}, b: 1}, {a: {$gt: 6}, b: 1}]}).sort({a: 1}) // Bounds generation planning generates OR plan: incorrect order.
      { "_id" : 1, "a" : [ 6, 10 ], "b" : 1 }
      { "_id" : 0, "a" : [ 8 ], "b" : 1 }
      

      Both of the above plans generate SORT <= COLLSCAN plans (as the collection has no indexes), but they differ in the bounds generation planning process. For the first query, bounds generation planning yields sort key bounds of (6, infinity), as a FETCH <= IXSCAN plan is generated where the index scan stage has these bounds. For the second query, bounds generation planning yields no sort key bounds, as an OR plan is generated and the SortStageKeyGenerator does not handle these plans.

            Assignee:
            Backlog - Query Team (Inactive)
            Reporter:
            J Rassi (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: