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

expressive $lookup "let" with missing field cannot be optimized

    XMLWordPrintableJSON

Details

    • Query Optimization

    Description

      *EDIT* Note that in aggregation expressions null and missing are not equal (i.e $eq of two of them will not return true) so technically this works "correctly" according to aggregation expression semantics, but it's not consistent with $lookup with localField/foreignField which uses find/match equality semantics.

      Discovered when debugging expressive lookup not using _id index when looking up based on non-existent field:

      {$lookup:{
          from:"coll",as:"x", 
          let:{i:"$foobar"},
          pipeline:[{$match:{$expr:{$eq:["$_id","$$i"]}}}]
      }}
      

      Because evaluation of "$foobar" in let doesn't resolve it to a constant expression, the inner pipeline cannot rewrite $expr and doesn't use an index.

      Reported on 3.6, tested and reproduced on 4.1.9.

      Attachments

        Activity

          People

            backlog-query-optimization Backlog - Query Optimization
            asya.kamsky@mongodb.com Asya Kamsky
            Votes:
            2 Vote for this issue
            Watchers:
            22 Start watching this issue

            Dates

              Created:
              Updated: