ExportXMLWordPrintableJSON

    • Type: Bug
    • Resolution: Unresolved
    • Priority: Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • Query Optimization
    • ALL
    • Hide

       

      /**
       * JOO wrong results: a $lookup whose "as" field equals (or is a prefix of) its own localField,
       * followed by a second $lookup that joins on the embedded field, returns no rows.
       *
       * The join graph has edges base.x = b1.y and b1.y = b2.y, from which an implicit edge
       * base.x = b2.y is inferred. The physical predicate for that implicit edge reads path "x" on the
       * output of the first join, but at that point "x" has already been overwritten by the embedded
       * b1 document, so the predicate never matches. PathResolver::trackEmbedPath() only checks the
       * embed path against base paths that were resolved *before* the $lookup; the $lookup's own
       * localField is resolved afterwards and so never trips the shadow check.
       *
       * Workaround: internalMaxNumberNodesConsideredForImplicitEdges=1 gives correct results.
       */
      const conn = MongoRunner.runMongod({
          setParameter: {
              internalEnableJoinOptimization: true,
              featureFlagPathArrayness: true,
              featureFlagCostBasedRanker: true,
              internalEnableJoinPlanCache: false,
          },
      });
      const db = conn.getDB("test");
      try {
          assert.commandWorked(db.a.insert({_id: 1, x: 1}));
          assert.commandWorked(db.b.insert({_id: 1, y: 1}));
          assert.commandWorked(db.a.createIndex({x: 1}));
          assert.commandWorked(db.b.createIndex({y: 1}));
      
          const pipeline = [
              {$lookup: {from: "b", localField: "x", foreignField: "y", as: "x"}},
              {$unwind: "$x"},
              {$lookup: {from: "b", localField: "x.y", foreignField: "y", as: "z"}},
              {$unwind: "$z"},
          ];
      
          function run(joinOpt) {
              assert.commandWorked(db.adminCommand({setParameter: 1, internalEnableJoinOptimization: joinOpt}));
              return db.a.aggregate(pipeline).toArray();
          }
          const expected = run(false);
          assert.eq(expected.length, 1, tojson(expected));
          const actual = run(true);
          // assert: [ ] != [ { "_id" : 1, "x" : { "_id" : 1, "y" : 1 }, "z" : { "_id" : 1, "y" : 1 } } ] : join optimization returned different results
          assert.sameMembers(actual, expected, "join optimization returned different results");
      } finally {
          MongoRunner.stopMongod(conn);
      }
      
      
      
      Show
        /** * JOO wrong results: a $lookup whose "as" field equals (or is a prefix of) its own localField, * followed by a second $lookup that joins on the embedded field, returns no rows. * * The join graph has edges base.x = b1.y and b1.y = b2.y, from which an implicit edge * base.x = b2.y is inferred. The physical predicate for that implicit edge reads path "x" on the * output of the first join, but at that point "x" has already been overwritten by the embedded * b1 document, so the predicate never matches. PathResolver::trackEmbedPath() only checks the * embed path against base paths that were resolved *before* the $lookup; the $lookup's own * localField is resolved afterwards and so never trips the shadow check. * * Workaround: internalMaxNumberNodesConsideredForImplicitEdges=1 gives correct results. */ const conn = MongoRunner.runMongod({ setParameter: { internalEnableJoinOptimization: true , featureFlagPathArrayness: true , featureFlagCostBasedRanker: true , internalEnableJoinPlanCache: false , }, }); const db = conn.getDB( "test" ); try { assert.commandWorked(db.a.insert({_id: 1, x: 1})); assert.commandWorked(db.b.insert({_id: 1, y: 1})); assert.commandWorked(db.a.createIndex({x: 1})); assert.commandWorked(db.b.createIndex({y: 1})); const pipeline = [ {$lookup: {from: "b" , localField: "x" , foreignField: "y" , as: "x" }}, {$unwind: "$x" }, {$lookup: {from: "b" , localField: "x.y" , foreignField: "y" , as: "z" }}, {$unwind: "$z" }, ]; function run(joinOpt) { assert.commandWorked(db.adminCommand({setParameter: 1, internalEnableJoinOptimization: joinOpt})); return db.a.aggregate(pipeline).toArray(); } const expected = run( false ); assert.eq(expected.length, 1, tojson(expected)); const actual = run( true ); // assert: [ ] != [ { "_id" : 1, "x" : { "_id" : 1, "y" : 1 }, "z" : { "_id" : 1, "y" : 1 } } ] : join optimization returned different results     assert.sameMembers(actual, expected, "join optimization returned different results" ); } finally { MongoRunner.stopMongod(conn); }
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      With {$lookup: {localField: "x", as: "x"}} followed by a $lookup on x.y, the inferred implicit edge base.x = b2.y evaluates path x after the first embedding has overwritten it, so no rows match. PathResolver::trackEmbedPath() only checks the embed path against base paths resolved before the $lookup, so the lookup's own localField never trips the shadow check. Setting internalMaxNumberNodesConsideredForImplicitEdges=1 gives correct results.

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

              Created:
              Updated: