ExportXMLWordPrintableJSON

    • Type: Bug
    • Resolution: Done
    • Priority: Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • Query Optimization
    • Fully Compatible
    • ALL
    • Hide
      
      /**
       * 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);
      }
      
      
      
      Show
      /** * 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); }
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      '{"arr.0": 1}'  before the first $lookup, in a $lookup sub-pipeline, or absorbed from after $unwind, is pushed into a join-node CanonicalQuery and executed in SBE, where getField(array, "0") does not index the array. Outside join optimization the parser caps SbeCompatibility for such paths (SERVER-49852) and classic runs the filter; createCQForJoinPipeline() calls setSbeCompatible(true) unconditionally.

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

              Created:
              Updated:
              Resolved: