/**
* JOO wrong results: a $match on a path with a numeric component (e.g. {"arr.0": 1}) that is
* pushed into a join node's CanonicalQuery is lowered to SBE, where getField(array, "0") does not
* index into the array, so the filter never matches.
*
* Outside join optimization such predicates are evaluated by the classic engine. The join
* optimizer marks every join-node CanonicalQuery SBE-compatible without checking the filter
* (createCQForJoinPipeline). The predicate can sit before the first $lookup, inside the $lookup
* sub-pipeline, or be the $match absorbed into $lookup+$unwind; all three lose the row.
*/
const conn = MongoRunner.runMongod({
setParameter: {
internalEnableJoinOptimization: true,
featureFlagPathArrayness: true,
featureFlagCostBasedRanker: true,
internalEnableJoinPlanCache: false,
},
});
const db = conn.getDB("test");
try {
assert.commandWorked(db.a.insertMany([{_id: 1, x: 1, arr: [1, 2]}, {_id: 2, x: 2, arr: [3]}]));
assert.commandWorked(db.b.insertMany([{_id: 1, y: 1, arr: [1, 2]}, {_id: 2, y: 2, arr: [3]}]));
assert.commandWorked(db.a.createIndex({x: 1}));
assert.commandWorked(db.b.createIndex({y: 1}));
const lookup = {$lookup: {from: "b", localField: "x", foreignField: "y", as: "b"}};
const pipelines = {
prefix: [{$match: {"arr.0": 1}}, lookup, {$unwind: "$b"}],
subPipeline: [
{$lookup: {from: "b", localField: "x", foreignField: "y", pipeline: [{$match: {"arr.0": 1}}], as: "b"}},
{$unwind: "$b"},
],
absorbed: [lookup, {$unwind: "$b"}, {$match: {"b.arr.0": 1}}],
};
for (const [name, pipeline] of Object.entries(pipelines)) {
assert.commandWorked(db.adminCommand({setParameter: 1, internalEnableJoinOptimization: false}));
const expected = db.a.aggregate(pipeline).toArray();
assert.eq(expected.length, 1, name + ": " + tojson(expected));
assert.commandWorked(db.adminCommand({setParameter: 1, internalEnableJoinOptimization: true}));
assert.sameMembers(db.a.aggregate(pipeline).toArray(), expected, name);
}
} finally {
MongoRunner.stopMongod(conn);
}