Found by an AI bug hunt and confirmed by a differential oracle (SBE vs classic).

Problem

When the foreign collection has a partial index and $lookup runs in SBE with the dynamic indexed loop join, the join probes the index whenever the local key passes the index's partial filter. It does not check that the index holds every foreign document the key matches. Foreign documents left out of the index are lost from the result. Classic returns them.

Repro

The test starts its own mongod with featureFlagSbeFull, runs the controls first and asserts the bug last. It fails at the last assertion on a build of HEAD bef4af6d7cd.

const conn = MongoRunner.runMongod({setParameter: {featureFlagSbeFull: true}});
const db = conn.getDB("test");
const c = db.c, f = db.f;
c.drop(); f.drop();
assert.commandWorked(c.insertMany([{_id: 1, a: null}, {_id: 2}, {_id: 3, a: 1}]));
assert.commandWorked(f.insertMany([{_id: 10, b: null}, {_id: 11}, {_id: 12, b: 1}, {_id: 13, x: 1}]));
const pipeline = [
    {$lookup: {from: "f", localField: "a", foreignField: "b", as: "m"}},
    {$project: {ids: "$m._id"}},
    {$sort: {_id: 1}},
];
const run = (engine, opts) => {
    assert.commandWorked(db.adminCommand({setParameter: 1, internalQueryFrameworkControl: engine}));
    const res = c.aggregate(pipeline, opts).toArray().map((d) => ({_id: d._id, ids: d.ids.sort()}));
    jsTest.log.info("run", {engine, res});
    return res;
};
const expected = [{_id: 1, ids: [10, 11, 13]}, {_id: 2, ids: [10, 11, 13]}, {_id: 3, ids: [12]}];

// Controls: without the partial index every engine returns the expected rows.
assert.eq(run("forceClassicEngine", {allowDiskUse: false}), expected);
assert.eq(run("trySbeEngine", {allowDiskUse: false}), expected);

assert.commandWorked(f.createIndex({b: 1}, {partialFilterExpression: {b: {$exists: true}}}));
// Classic is the reference and is unaffected by the index.
assert.eq(run("forceClassicEngine", {allowDiskUse: false}), expected);
// The SBE engine ran, and used the index.
assert.commandWorked(db.adminCommand({setParameter: 1, internalQueryFrameworkControl: "trySbeEngine"}));
const explain = JSON.stringify(c.explain().aggregate(pipeline, {allowDiskUse: false}));
assert(explain.includes("slotBasedPlan") && explain.includes("IXSCAN"));
// Bug (asserted last): SBE returns [10] for _id 1 and 2 (foreign docs 11 and 13 have no "b").
assert.eq(run("trySbeEngine", {allowDiskUse: false}), expected);
MongoRunner.stopMongod(conn);

A null or missing local key matches foreign documents whose field is null or missing. Documents 11 and 13 have no b, so they are not in the partial index and the SBE join never sees them. Without the partial index both engines return the classic result.

The explain of the repro shows slotBasedPlan, IXSCAN and the strategy DynamicIndexedLoopJoin. The query uses allowDiskUse false, which (from code reading) keeps the planner from choosing hash join here. In the oracle run, every SBE configuration returned the same wrong rows, including the four join-strategy flag configurations. The default engine also ran this query in SBE.

Second shape (lower confidence, no standalone repro)

Index b: 1 with partialFilterExpression b: {$gt: 5}, local a: [[7]], foreign document b: [[7]]. In one oracle run with allowDiskUse false, classic matched the foreign document and SBE did not. A variant of this shape without allowDiskUse false (hash join) matched classic and differed only when hash join was disabled, so the result depends on the join strategy. Treat it as a hint for the owners, not as a confirmed second bug.

Cause (from code reading, not verified further)

buildDynamicIndexedLoopJoinLookupStage (gen_lookup.cpp, around L1233-1290) builds a gate for the index branch. The null-type guard is added for sparse indexes only (L1238). For a partial index the gate requires exactly one local key and that the key passes the partial filter (L1257-1290). A null or missing local key is the single key null, and $exists is true for a null value, so the document takes the index branch even though foreign documents without the field are not in the index. This is the same kind of gap the sparse-index guard closed (SERVER-116135) in the code added for partial indexes (SERVER-116515). I did not read those two tickets.

Impact

Silent wrong results: rows missing from the as array. Needs a partial foreign index whose filter excludes some documents the key can match, and conditions that make the planner choose this join (here allowDiskUse false).

Related tickets

No match found in SERVER in any status (searches on lookup with partial index, DynamicIndexedLoopJoin, partialFilterExpression). Related background: SERVER-65665 (Closed, partial indexes disallowed in this join in 2022; summary read only).

Suggested priority: P2 (silent wrong results, narrow shape). Label: found-by-ai-hunter. Assigned team: 10gen/query-execution-sbe (OWNERS.yml of stage_builder/sbe).