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

$lookup pipeline ignores index when a let clause variable is NULL

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 7.2.2
    • Component/s: None
    • None
    • Query Optimization
    • ALL
    • Hide

      See description

      Show
      See description

      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

            Assignee:
            chris.kelly@mongodb.com Chris Kelly
            Reporter:
            dk@targetchoice.com Dmitriy Kruglyak
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: