Join optimization drops $lookup embedding with $set/$addFields above residual $match

ExportXMLWordPrintableJSON

    • Type: Bug
    • Resolution: Unresolved
    • Priority: Minor - P4
    • None
    • Affects Version/s: None
    • Component/s: None
    • Query Optimization
    • ALL
    • Hide 
      const conn = MongoRunner.runMongod({
    setParameter: {
        internalEnableJoinOptimization: true,
        featureFlagPathArrayness: true,
        featureFlagSbeFull: true,
    },
});
const db = conn.getDB("test");
assert.commandWorked(db.local.insertMany([{_id: 1, k: 1, t: "a"}, {_id: 2, k: 2, t: "b"}]));
assert.commandWorked(db.foreign.insertMany([{_id: 10, k: 1, v: "x"}, {_id: 20, k: 2, v: "y"}]));
assert.commandWorked(db.local.createIndex({k: 1}));
assert.commandWorked(db.foreign.createIndex({k: 1}));
const pipeline = [
    {$lookup: {from: "foreign", localField: "k", foreignField: "k", as: "out"}},
    {$unwind: "$out"},
    {$set: {flag: 1}},
    {$match: {$or: [{t: {$ne: "zzz"}}, {"out.v": "x"}]}},
];
const withJoinOpt = db.local.aggregate(pipeline).toArray();
assert.commandWorked(db.adminCommand({setParameter: 1, internalEnableJoinOptimization: false}));
const withoutJoinOpt = db.local.aggregate(pipeline).toArray();
printjson({withJoinOpt, withoutJoinOpt});
MongoRunner.stopMongod(conn);

// Output
// withJoinOpt:    {_id: 1, k: 1, t: "a", flag: 1}
// withoutJoinOpt: {_id: 1, k: 1, t: "a", out: {_id: 10, k: 1, v: "x"}, flag: 1}
      Show
      const conn = MongoRunner.runMongod({     setParameter: {         internalEnableJoinOptimization: true ,         featureFlagPathArrayness: true ,         featureFlagSbeFull: true ,     }, }); const db = conn.getDB( "test" ); assert .commandWorked(db.local.insertMany([{_id: 1, k: 1, t: "a" }, {_id: 2, k: 2, t: "b" }])); assert .commandWorked(db.foreign.insertMany([{_id: 10, k: 1, v: "x" }, {_id: 20, k: 2, v: "y" }])); assert .commandWorked(db.local.createIndex({k: 1})); assert .commandWorked(db.foreign.createIndex({k: 1})); const pipeline = [     {$lookup: {from: "foreign" , localField: "k" , foreignField: "k" , as: "out" }},     {$unwind: "$out" },     {$set: {flag: 1}},     {$match: {$or: [{t: {$ne: "zzz" }}, { "out.v" : "x" }]}}, ]; const withJoinOpt = db.local.aggregate(pipeline).toArray(); assert .commandWorked(db.adminCommand({setParameter: 1, internalEnableJoinOptimization: false })); const withoutJoinOpt = db.local.aggregate(pipeline).toArray(); printjson({withJoinOpt, withoutJoinOpt}); MongoRunner.stopMongod(conn); // Output // withJoinOpt:    {_id: 1, k: 1, t: "a" , flag: 1} // withoutJoinOpt: {_id: 1, k: 1, t: "a" , out: {_id: 10, k: 1, v: "x" }, flag: 1}
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      Note: only when featureFlagSbeFull=true.

      A $lookup+$unwind followed by $set (or $addFields) and a $match that references the as field returns documents without the embedded foreign document. Removing either the $set or the $match gives correct results.

      Found by lookup_unwind_pbt.js in the all-feature-flags-sbe-full variant with join opt enabled.

            Assignee:
            Unassigned
            Reporter:
            Max Verbinnen
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated: