[SERVER-81555] Investigate why $lookup inner pipeline uses _id index when local collection collation does not match foreign collection collation Created: 29/Sep/23  Updated: 31/Jan/24  Resolved: 31/Jan/24

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Rahul Sharma Assignee: Rushan Chen
Resolution: Works as Designed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
Assigned Teams:
Query Execution
Operating System: ALL
Participants:

 Description   

Mongod version: 7.0.1

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, $ookup 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.


Generated at Thu Feb 08 06:46:49 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.