Incorrect results because base 'localField' is misread after reordered $lookup shadows its path prefix

XMLWordPrintableJSON

    • Type: Bug
    • Resolution: Unresolved
    • Priority: Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • Query Optimization
    • ALL
    • Hide 
      /**
 * @tags: [
 *   requires_fcv_90,
 *   requires_sbe,
 * ]
 */

(function() {
"use strict";

const conn = MongoRunner.runMongod({
    setParameter: {
        featureFlagPathArrayness: true,
        internalEnablePathArrayness: true,
        internalEnableJoinOptimization: false,
        internalJoinReorderMode: "random",
        internalRandomJoinOrderSeed: 0,
        internalQueryFrameworkControl: "trySbeEngine",
    },
});
assert.neq(null, conn);
const testDB = conn.getDB(jsTestName());
const baseColl = testDB.base;
const matchColl = testDB.match;
const shadowColl = testDB.shadow;

try {
    baseColl.drop();
    matchColl.drop();
    shadowColl.drop();

    assert.commandWorked(baseColl.insertOne({_id: 0, a: {x: 1}, key: 1}));
    assert.commandWorked(matchColl.insertOne({_id: 0, k: 1}));
    assert.commandWorked(shadowColl.insertOne({_id: 0, key: 1, x: -1}));  

    assert.commandWorked(baseColl.createIndexes([{key: 1}, {"a.x": 1}]));
    assert.commandWorked(matchColl.createIndex({k: 1}));
    assert.commandWorked(shadowColl.createIndex({key: 1}));
    
    const pipeline = [
        {$lookup: {from: matchColl.getName(), localField: "a.x", foreignField: "k", as: "matched"}},
        {$unwind: "$matched"},
        {$lookup: {from: shadowColl.getName(), localField: "key", foreignField: "key", as: "a"}},
        {$unwind: "$a"},
    ];
    const noOptResult = baseColl.aggregate(pipeline).toArray();
    assert.eq(noOptResult.length, 1, "Reference (no join opt): expected 1 document but got " + tojson(noOptResult));    assert.commandWorked(testDB.adminCommand({setParameter: 1, internalEnableJoinOptimization: true}));

    const optResult = baseColl.aggregate(pipeline).toArray();
    assert.eq(
        optResult.length,
        1,
        "With join optimization enabled: expected 1 document but got " +
            tojson(optResult) +
            '. The reordered plan joins $lookup{as:"a"} before $lookup{localField:"a.x"}, so ' +
            "the localField predicate must still read base.a.x, not the embedded a.x.",
    );
} finally {
    MongoRunner.stopMongod(conn);
}
})();
      Show
      /**  * @tags: [  *   requires_fcv_90,  *   requires_sbe,  * ]  */ (function() { "use strict" ; const conn = MongoRunner.runMongod({     setParameter: {         featureFlagPathArrayness: true ,         internalEnablePathArrayness: true ,         internalEnableJoinOptimization: false ,         internalJoinReorderMode: "random" ,         internalRandomJoinOrderSeed: 0,         internalQueryFrameworkControl: "trySbeEngine" ,     }, }); assert .neq( null , conn); const testDB = conn.getDB(jsTestName()); const baseColl = testDB.base; const matchColl = testDB.match; const shadowColl = testDB.shadow; try {     baseColl.drop();     matchColl.drop();     shadowColl.drop(); assert .commandWorked(baseColl.insertOne({_id: 0, a: {x: 1}, key: 1}));     assert .commandWorked(matchColl.insertOne({_id: 0, k: 1}));     assert .commandWorked(shadowColl.insertOne({_id: 0, key: 1, x: -1}));  assert .commandWorked(baseColl.createIndexes([{key: 1}, { "a.x" : 1}]));     assert .commandWorked(matchColl.createIndex({k: 1}));     assert .commandWorked(shadowColl.createIndex({key: 1})); const pipeline = [         {$lookup: {from: matchColl.getName(), localField: "a.x" , foreignField: "k" , as: "matched" }},         {$unwind: "$matched" },         {$lookup: {from: shadowColl.getName(), localField: "key" , foreignField: "key" , as: "a" }},         {$unwind: "$a" },     ]; const noOptResult = baseColl.aggregate(pipeline).toArray();     assert .eq(noOptResult.length, 1, "Reference (no join opt): expected 1 document but got " + tojson(noOptResult));    assert .commandWorked(testDB.adminCommand({setParameter: 1, internalEnableJoinOptimization: true })); const optResult = baseColl.aggregate(pipeline).toArray();     assert .eq(         optResult.length,         1,         "With join optimization enabled: expected 1 document but got " +             tojson(optResult) +             '. The reordered plan joins $lookup{as: "a" } before $lookup{localField: "a.x" }, so ' +             "the localField predicate must still read base.a.x, not the embedded a.x." ,     ); } finally {     MongoRunner.stopMongod(conn); } })();
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      Join optimization can return incorrect results when a $lookup uses a base collection dotted localField, and a later $lookup writes to the same top-level path via as.

      In the attached repro, the original pipeline first evaluates localField: "a.x" against the base document, where base.a.x == 1, then later embeds another lookup result at as: "a". Without join optimization, the pipeline returns one document.

      With join optimization enabled and the joins reordered, the as: "a" lookup runs before the lookup whose predicate was resolved from base.a.x. The physical predicate still evaluates raw path "a.x", so it reads the newly embedded foreign document’s a.x == -1 instead of the original base field. The lookup then fails to match and returns zero documents.

      Expected: reordered join plans preserve the original meaning of base localField paths.

      Actual: reordered plan can evaluate the path after it has been shadowed by an embedded lookup result, producing incorrect results.

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

              Created:
              Updated: