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

Investigate why $lookup inner pipeline uses _id index when local collection collation does not match foreign collection collation

    XMLWordPrintableJSON

Details

    • Icon: Bug Bug
    • Resolution: Works as Designed
    • Icon: Major - P3 Major - P3
    • None
    • None
    • None
    • None
    • Query Execution
    • ALL

    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.

      Attachments

        Activity

          People

            rushan.chen@mongodb.com Rushan Chen
            r.sharma@mongodb.com Rahul Sharma
            Votes:
            0 Vote for this issue
            Watchers:
            16 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: