Wrong result with SBE, hash index, $lookup

XMLWordPrintableJSON

    • Query Execution
    • Fully Compatible
    • ALL
    • v8.0, v7.0, v6.0
    • Hide

      In comment.

      Show
      In comment.
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      The following pipeline:

      db.fuzzer_coll.aggregate([
{
    "$sort": {
        "_id": 1
    }
}, {
    "$limit": 1
},
 {
    "$lookup": {
        "from": "fuzzer_coll",
        "localField": "foo_bar_baz",
        "foreignField": "obj.obj.date",
        "as": "lookup_return_array"
    }
}
]).toArray();

      , if executed under SBE, with a hash index on `obj.obj.date`, returns the following incorrect result:

        {
    _id: 0,
    bool: null,
    str: 'program software deposit',
    num: Long('94931'),
    date: ISODate('2020-01-04T18:08:08.938Z'),
    array: [
      'interface concept demand-driven',
      Decimal128('360.5873021178402'),
      null,
      'process improvement California Licensed Steel Chips',
      ISODate('2019-10-20T14:40:00.541Z'),
      false,
      Decimal128('-590.8266392562547'),
      ISODate('2019-12-19T22:47:39.000Z'),
      'Balanced Awesome monitor',
      'bricks-and-clicks Fantastic',
      {
        _id: 1,
        str: 'Quality',
        num: 93794,
        array: [],
        any: ISODate('2019-09-24T21:22:08.235Z')
      }
    ],
    obj: {
      _id: 2,
      bool: false,
      str: null,
      num: Long('48829'),
      date: ISODate('2019-09-16T11:26:55.140Z'),
      geoLegacy: { x: 10.679373662169098, y: -78.55198706267569 },
      any: Decimal128('528.9198650392352')
    },
    lookup_return_array: []
  }
]

      the `lookup_return_array` is empty, indicating that no rows matched, which is hard to believe since `foo_bar_baz` is always non-existing on the outer side and `obj.obj.date` does not always exist on the inner side. The non SBE plan for this query does return a non-empty `lookup_return_array`, which is more plausible.

        1. repro.js
          0.8 kB
        2. SERVER-92668-populate-and-sleep.js
          2.92 MB

            Assignee:
            Kevin Cherkauer (Inactive)
            Reporter:
            Philip Stoev
            Votes:
            0 Vote for this issue
            Watchers:
            19 Start watching this issue

              Created:
              Updated:
              Resolved: