ExportXMLWordPrintableJSON

    • Type: Bug
    • Resolution: Unresolved
    • Priority: Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • Query Optimization
    • ALL
    • Hide
      
      /**
       * JOO wrong results: the $match absorbed into a $lookup+$unwind (the "additional filter") is
       * applied *below* the $lookup's own sub-pipeline $project instead of above it.
       *
       * extractPredicatesFromLookup() parses getAbsorbedFilter() and prepends it to the foreign node's
       * CanonicalQuery pipeline via addInitialSource(), ahead of any $project/$addFields the user wrote
       * in the sub-pipeline. Without join optimization the absorbed filter runs after the sub-pipeline,
       * so a filter on a field computed, renamed or removed by that $project behaves differently.
       */
      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}, {_id: 2, x: 2}]));
          assert.commandWorked(db.b.insertMany([{_id: 1, y: 1, w: 5}, {_id: 2, y: 2, w: 6}]));
          assert.commandWorked(db.a.createIndex({x: 1}));
          assert.commandWorked(db.b.createIndex({y: 1}));
      
          function run(pipeline, joinOpt) {
              assert.commandWorked(db.adminCommand({setParameter: 1, internalEnableJoinOptimization: joinOpt}));
              return db.a.aggregate(pipeline).toArray();
          }
      
          // Filter on a field renamed by the sub-pipeline $project: JOO filters on "ww" before it exists.
          const renamePipeline = [
              {$lookup: {from: "b", localField: "x", foreignField: "y", pipeline: [{$project: {y: 1, ww: "$w"}}], as: "b"}},
              {$unwind: "$b"},
              {$match: {"b.ww": 5}},
          ];
          let expected = run(renamePipeline, false);
          assert.eq(expected.length, 1, tojson(expected));
          // assert: [ ] != [ { "_id" : 1, "x" : 1, "b" : { "_id" : 1, "y" : 1, "ww" : 5 } } ] : renamed field
          assert.sameMembers(run(renamePipeline, true), expected, "renamed field");
      
          // Filter on a field excluded by the sub-pipeline $project: JOO filters before the exclusion.
          const exclusionPipeline = [
              {$lookup: {from: "b", localField: "x", foreignField: "y", pipeline: [{$project: {w: 0}}], as: "b"}},
              {$unwind: "$b"},
              {$match: {"b.w": {$exists: false}}},
          ];
          expected = run(exclusionPipeline, false);
          assert.eq(expected.length, 2, tojson(expected));
          assert.sameMembers(run(exclusionPipeline, true), expected, "excluded field");
      } finally {
          MongoRunner.stopMongod(conn);
      }
      
      
      
      Show
      /** * JOO wrong results: the $match absorbed into a $lookup+$unwind (the "additional filter" ) is * applied *below* the $lookup's own sub-pipeline $project instead of above it. * * extractPredicatesFromLookup() parses getAbsorbedFilter() and prepends it to the foreign node's * CanonicalQuery pipeline via addInitialSource(), ahead of any $project/$addFields the user wrote * in the sub-pipeline. Without join optimization the absorbed filter runs after the sub-pipeline, * so a filter on a field computed, renamed or removed by that $project behaves differently. */ 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}, {_id: 2, x: 2}])); assert.commandWorked(db.b.insertMany([{_id: 1, y: 1, w: 5}, {_id: 2, y: 2, w: 6}])); assert.commandWorked(db.a.createIndex({x: 1})); assert.commandWorked(db.b.createIndex({y: 1})); function run(pipeline, joinOpt) { assert.commandWorked(db.adminCommand({setParameter: 1, internalEnableJoinOptimization: joinOpt})); return db.a.aggregate(pipeline).toArray(); } // Filter on a field renamed by the sub-pipeline $project: JOO filters on "ww" before it exists. const renamePipeline = [ {$lookup: {from: "b" , localField: "x" , foreignField: "y" , pipeline: [{$project: {y: 1, ww: "$w" }}], as: "b" }}, {$unwind: "$b" }, {$match: { "b.ww" : 5}}, ]; let expected = run(renamePipeline, false ); assert.eq(expected.length, 1, tojson(expected)); // assert: [ ] != [ { "_id" : 1, "x" : 1, "b" : { "_id" : 1, "y" : 1, "ww" : 5 } } ] : renamed field assert.sameMembers(run(renamePipeline, true ), expected, "renamed field" ); // Filter on a field excluded by the sub-pipeline $project: JOO filters before the exclusion. const exclusionPipeline = [ {$lookup: {from: "b" , localField: "x" , foreignField: "y" , pipeline: [{$project: {w: 0}}], as: "b" }}, {$unwind: "$b" }, {$match: { "b.w" : {$exists: false }}}, ]; expected = run(exclusionPipeline, false ); assert.eq(expected.length, 2, tojson(expected)); assert.sameMembers(run(exclusionPipeline, true ), expected, "excluded field" ); } finally { MongoRunner.stopMongod(conn); }
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      extractPredicatesFromLookup() parses getAbsorbedFilter() and prepends it to the foreign CanonicalQuery pipeline with addInitialSource(), ahead of any user $project/$addFields in the sub-pipeline. A filter on a field renamed or excluded by that $project therefore evaluates against the pre-projection document and returns wrong results. Introduced by SERVER-125579.

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

              Created:
              Updated: