Join Optimisation does not handle correctly queries with preserveNullAndEmptyArrays when the foreign collection is empty and the strategy is Indexed Nested Loop

XMLWordPrintableJSON

    • Type: Bug
    • Resolution: Fixed
    • Priority: Major - P3
    • 8.3.0-rc0
    • Affects Version/s: None
    • Component/s: None
    • None
    • Query Execution
    • Fully Compatible
    • ALL
    • 200
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      When the foreign collection does not have data but there is an index on it, even if the index is not on the join field, JO will run an Indexed Nested Loop Join and will return wrong results.

      Example

      db.countries.insertMany([  
  { "_id": 1, "name": "USA" },  
  { "_id": 2, "name": "Canada" },  
  { "_id": 3, "name": "France" }  
]); 

db.cities.createIndex({ "cityName": 1 });

db.countries.aggregate([  
  {  
    "$lookup": {  
      "from": "cities",
      "localField": "_id", 
      "foreignField": "countryId",
      "as": "cities"
    }  
  },  
  {  
    "$unwind": {  
      "path": "$cities", 
      "preserveNullAndEmptyArrays": true
    }  
  }
]);

      Expected results

      [
  { _id: 1, name: 'USA' },
  { _id: 2, name: 'Canada' },
  { _id: 3, name: 'France' }
]

      JO results

      db.adminCommand({ setParameter: 1, internalEnableJoinOptimization: true });

[]

      Even if we drop the index by calling db.cities.dropIndexes(); It will still execute an Indexed nested loop using the index on _id.

            Assignee:
            Foteini Alvanaki
            Reporter:
            Foteini Alvanaki
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved: