Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-92668

Wrong result with SBE, hash index, $lookup

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

      In comment.

      Show
      In comment.

      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@mongodb.com Kevin Cherkauer
            Reporter:
            philip.stoev@mongodb.com Philip Stoev
            Votes:
            0 Vote for this issue
            Watchers:
            19 Start watching this issue

              Created:
              Updated:
              Resolved: