Filed by the AI query bug hunter (qhunt), an agent that reviews query code for corner-case bugs. A multi-version performance oracle and the repro below confirmed the issue.

Problem

When the localField of a $lookup is an array with duplicate values and SBE runs the join as a HashJoin, each outer row probes the hash table once per array element, duplicates included. Each probe appends all matching foreign buffer indices to one vector, which is then sorted and de-duplicated. For an array of D elements whose values each match M foreign documents, each outer row inserts and sorts D x M indices. 9.0 converted the local key to a set first, so it probes once per distinct value. With default settings (trySbeRestricted), a $lookup on an unindexed foreign field runs in SBE as a HashJoin, so this shape runs in SBE.

Repro

Fails at master d0787dfc0ca (2026-10-08 nightly) and passes on 9.0.2. 25 local documents, each with a 2000-element array holding one repeated value; 10000 foreign documents over 10 distinct keys, no index on the join field:

/**
 * F008: SBE hash-join $lookup probes the hash table once per element of the local array key,
 * including duplicates, instead of once per distinct value. A local array with many duplicate
 * elements makes each outer row do thousands of probes and sort the combined matches.
 *
 * @tags: [
 *   requires_fcv_90,
 *   requires_sbe
 * ]
 */

const conn = MongoRunner.runMongod({setParameter: {internalQueryDisablePlanCache: true}});
const db = conn.getDB(jsTestName());

// 10000 foreign docs over 10 distinct keys; no index on the join field, so SBE picks a hash join.
let batch = [];
for (let i = 0; i < 10000; i++) batch.push({_id: i, fk: i % 10, other: i});
assert.commandWorked(db.foreign.insert(batch));
// Each local doc has a 2000-element array holding a single repeated value.
batch = [];
for (let i = 0; i < 25; i++) batch.push({_id: i, lk: Array(2000).fill(i % 10)});
assert.commandWorked(db.local.insert(batch));

const pipeline = [{$lookup: {from: "foreign", localField: "lk", foreignField: "fk", as: "m"}}, {$count: "c"}];

function run(mode) {
    assert.commandWorked(db.adminCommand({setParameter: 1, internalQueryFrameworkControl: mode}));
    const s = tojson(db.local.explain().aggregate(pipeline));
    const engine = s.includes("slotBasedPlan") ? "sbe" : "classic";
    const strategy = (s.match(/"strategy" : "([A-Za-z]+)"/) || [])[1];
    db.local.aggregate(pipeline).toArray();
    const t = [];
    for (let i = 0; i < 5; i++) {
        const t0 = Date.now();
        assert.eq(db.local.aggregate(pipeline).toArray()[0].c, 25);
        t.push(Date.now() - t0);
    }
    return {mode, engine, strategy, ms: t.sort((a, b) => a - b)[2]};
}

const classic = run("forceClassicEngine");
const sbe = run("trySbeRestricted");
jsTest.log.info("F008 timings", {classic, sbe, ratio: sbe.ms / Math.max(classic.ms, 1)});

// Controls: the two settings run the two engines, and SBE uses the hash join.
assert.eq(classic.engine, "classic", classic);
assert.eq(sbe.engine, "sbe", sbe);
assert.eq(sbe.strategy, "HashJoin", sbe);
// Fails at master d0787dfc0ca (SBE about 18x slower than classic); passes on 9.0.2, where
// SBE is faster than classic for this shape.
assert.lt(sbe.ms / Math.max(classic.ms, 1), 3, {classic, sbe});

MongoRunner.stopMongod(conn);

Results of the repro (median of 5 runs, single mongod, plan cache off):

The same shape on all builds at once (enterprise amazon2023 aarch64, one host, runs interleaved, foreign collection fixed at 10000 documents):

Master is 88-140x slower than 9.0.2 and 18.5x slower than classic on master. Bootstrap intervals are within 2% of each median. Results match on every side.

Cause (from code reading, not bisected)

• buildKeySetForLocal() in src/mongo/db/query/stage_builder/sbe/gen_lookup.cpp applies kArrayToSet only for KeyType::kArraySet. The comment says the de-duplication "can be skipped on the hash-join path, because the hash lookup implementation already de-duplicates the keys while probing". That came in with SERVER-132094 (ecb3471b014, 2026-07-29).
• LookupHashTableIter::initSearchArray() in src/mongo/db/exec/sbe/stages/lookup_hash_table.cpp de-duplicates the matched buffer indices, not the keys. It calls find() once per array element and appends every match to _hashTableMatchVector, then runs std::sort and std::unique over the whole vector. SERVER-132095 (de2737d124a, 2026-07-29) replaced the std::set here with this vector, so the vector now holds all D x M entries before the sort.
• In the repro, D = 2000 and M = 1000, so each outer row sorts 2M indices to produce 1000.

Neither commit is on v9.0, which matches the release results. The code path is unchanged on master e3b5216b2ce.

Impact

$lookup with an array localField that holds repeated values (tags, category ids, event types) runs on the default engine at a cost proportional to the array length times the matches per value, instead of the number of distinct values. Arrays without duplicates are not affected. The regression is on master only and has not shipped in a release. No sys-perf workload covers a hash-join $lookup with duplicate array keys.