$lookup with a view for the "from" field can return incorrect results on a sharded cluster

XMLWordPrintableJSON

    • Type: Bug
    • Resolution: Fixed
    • Priority: Major - P3
    • 9.1.0-rc0, 9.0.0-rc3
    • Affects Version/s: None
    • Component/s: None
    • None
    • Query Integration
    • Fully Compatible
    • ALL
    • v9.0
    • Hide 
      import {ShardingTest} from "jstests/libs/shardingtest.js";

const st = new ShardingTest({shards: 1});
const db = st.s.getDB("test");

assert.commandWorked(db.foreign.insert({}));
assert.commandWorked(db.createView("v", "foreign", [{$addFields: {b: 0}}]));
assert.commandWorked(db.local.insert({a: 0}));

const res =
    db.local.aggregate([{$lookup: {from: "v", localField: "a", foreignField: "b", as: "c"}}])
        .toArray();
// We should have matched the document in db.v
assert.eq(res[0].c.length, 1, res);

st.stop();
      Show
      import {ShardingTest} from "jstests/libs/shardingtest.js" ; const st = new ShardingTest({shards: 1}); const db = st.s.getDB( "test" ); assert .commandWorked(db.foreign.insert({})); assert .commandWorked(db.createView( "v" , "foreign" , [{$addFields: {b: 0}}])); assert .commandWorked(db.local.insert({a: 0})); const res = db.local.aggregate([{$lookup: {from: "v" , localField: "a" , foreignField: "b" , as: "c" }}]) .toArray(); // We should have matched the document in db.v assert .eq(res[0].c.length, 1, res); st.stop();
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      In the repro, we have a sharded collection, and define a view that sets b=0. This document should get matched in our lookup query, but does not for some reason. This bug repros on master and 9.0, but not 8.3.7

      The $lookup executes in classic

      A quick claude session says the following:

      Root-caused. It's a router→shard serialization gap, not a bug in $lookup execution itself.

      What happens

      The failing shape is: $lookup with localField/foreignField only (no user pipeline) against a view whose pipeline computes the foreign field.

      Standalone works (verified — returns the joined doc). Sharded fails. I profiled the shard and captured exactly what mongos sends on the view-resolution retry:

      {$lookup: {from: "foreign", as: "c", localField: "a", foreignField: "b",
                 $_internalFromIsAView: true, let: {},
                 pipeline: [\{$addFields: {b: {$const: 0}}}]}}

      Note what's missing: $_internalFieldMatchPipelineIdx. The router prepends the view's stages into pipeline (document_source_lookup.cpp:1191) but the guard that emits the placeholder index requires _userPipeline to be set (document_source_lookup.cpp:1110), which it isn't for lf/ff-only syntax. So the shard is told what the view stages are, but not where the equality $match belongs.

      On the shard, from is now the concrete collection, so _fromNsIsAView is false at construction and resolvedPipelineHelper (:363-373) computes _fieldMatchPipelineIdx = 0. The subpipeline becomes:

      [\{$match: {b: 0}}, \{$addFields: {b: 0}}]   // should be the reverse

      The join predicate runs before the view computes b, so nothing matches → c: [].

      Two secondary bugs found while confirming this

      I tried hand-feeding the missing index to a standalone mongod ($_internalFieldMatchPipelineIdx: 1 and 2, with $_internalFromIsAView: true). Both still returned c.length = 0, because of:

      1. createFromStageParams:634 — when internalFromIsAView && hasLocalFieldForeignFieldJoin() && internalFieldMatchPipelineIdx, it discards the router's index and recomputes via computeDesugaredMongotFieldMatchIdx(). For a non-mongot pipeline there is no source/support stage, so it hits the !lastPrefixIdx fallback at :556 returning size() - 1. This branch is only correct for mongot subpipelines; it has no non-mongot guard. (Active here — featureFlagExtensionsInsideHybridSearch is on in the shard command.)
      2. relocateFieldMatchPlaceholder:517-518 — the if (newIdx > oldIdx) --newIdx; decrement. The router's index is a position in the final placeholder-containing pipeline, so decrementing after the erase lands the $match one slot too early: target 1 on [$match{}, $addFields] yields [$match, $addFields] again.

      So even the pipeline-syntax-plus-lf/ff variant on a computed-field view is suspect, not just the no-pipeline case.

       

       

            Assignee:
            Finley Lau
            Reporter:
            Matt Boros
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: