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

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

    • Query Optimization

      *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.

            Assignee:
            backlog-query-optimization [DO NOT USE] Backlog - Query Optimization
            Reporter:
            asya.kamsky@mongodb.com Asya Kamsky
            Votes:
            4 Vote for this issue
            Watchers:
            25 Start watching this issue

              Created:
              Updated: