-
Type: Bug
-
Resolution: Duplicate
-
Priority: Major - P3
-
None
-
Affects Version/s: 7.2.2
-
Component/s: None
-
None
-
Query Optimization
-
ALL
-
Let's say we have two collections, widgets and features
Every widgets record has two fields primary_feature and secondary_feature, which store some natural id (assume all numeric and indexed). Primary one is guaranteed to be non-NULL, while the secondary one sometimes/ often could be NULL or undefined or unset
We need to perform a $lookup aggregation on widgets to pull up all relevant features, filtered/ transformed by certain criteria in subsequent pipeline stages. To do that we use a pipeline clause with a let variable for each of primary_feature and secondary_feature
While primary_feature performs properly by using index, records where secondary_feature equals NULL trigger a colscan. This makes this $lookup pipeline unusable for all practical purposes
We also found that if we are not using the pipeline clause and instead rely on localField/ foreignField clauses the indices are used properly even when secondary_feature equals NULL. We are using this workaround for now, but this leads to complicated/ fragile $filter/ $map operations for data clean up, and might not be viable at all for certain data sets
Please fix the $lookup pipelines to always use available indices even when a let variable is NULL
- duplicates
-
SERVER-40362 expressive $lookup "let" with missing field cannot be optimized
- Backlog