-
Type: Improvement
-
Resolution: Unresolved
-
Priority: Major - P3
-
None
-
Affects Version/s: None
-
Component/s: Aggregation Framework
-
Query Optimization
-
(copied to CRM)
*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.
- is duplicated by
-
SERVER-42738 Slow $lookup on $expr match with null field
- Closed
-
SERVER-89240 $lookup pipeline ignores index when a let clause variable is NULL
- Closed
- related to
-
SERVER-34927 allow localField and foreignField with more expressive $lookup pipeline
- Closed