Mongod version: 7.0.1

Update:

Reopening to track occurence. Curently there is no plan to implement any of the proposals described in the summary note, since the workaround including correcting the schema is much more preferrable. See the note for explanation and advice for the customers.

Issue:

The logic to determine that an index is eligible for indexed loop to join strategy when we push down $lookup to the SBE checks if the local collection's collator is same as the foreign collection's collator. If the collations are different, $lookup SBE lowering logic does not utilize the index when written with Equality Match with a Single Join Condition

The behaviour is not observed when we write queries with Correlated Subqueries Using Concise Syntax. In other words, the classic engine uses the _id_ index for the inner query of $lookup.

we need to confirm why we do see the difference in behaviour for different syntax in which One syntax is able to use the index while other is not.

For instance:

  {$lookup: { from: "a", localField: "_id",
              foreignField: "_id", as: "b" }}
 

The above syntax does not use the _id_ index but the following syntax uses the _id_ index which seems to be a bug because if the collator is different, it is likely that incorrect results are produced for string equality comparison.

   {$lookup: { from: "a",
               let: {x: "$_id"},
               pipeline: [ {$match: {$expr: {$eq: ["$_id", "$$x"]}}} ],
               as: "b" }}

 
Sample documents and collection collator information from the customer can be found at the linked HELP ticket.