ExportXMLWordPrintableJSON

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

       

      
      /**
       * JOO wrong results (minor, deprecated BSON undefined): an $expr equi-join treats a BSON
       * "undefined" value as different from a missing field, while aggregation $eq (classic $lookup and
       * SBE $lookup without join optimization) treats them as equal.
       *
       * The hash join buckets fillEmpty(path, null): undefined stays undefined and never meets null, and
       * the index probe seeks undefined while the index stores missing as null.
       */
      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: undefined}, {_id: 3}]));
          assert.commandWorked(db.b.insertMany([{_id: 1, y: undefined}, {_id: 3}]));
          assert.commandWorked(db.a.createIndex({x: 1}));
          assert.commandWorked(db.b.createIndex({y: 1}));
      
          const pipeline = [
              {$lookup: {from: "b", let: {v: "$x"}, pipeline: [{$match: {$expr: {$eq: ["$y", "$$v"]}}}], as: "b"}},
              {$unwind: "$b"},
              {$project: {_id: 1, bid: "$b._id"}},
          ];
          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, 4, tojson(expected));
          // assert: [ { "_id" : 1, "bid" : 1 }, { "_id" : 3, "bid" : 3 } ] != [ { "_id" : 1, "bid" : 1 }, { "_id" : 1, "bid" : 3 }, { "_id" : 3, "bid" : 1 }, { "_id" : 3, "bid" : 3 } ] : join optimization returned different results
          assert.sameMembers(run(true), expected, "join optimization returned different results");
      } finally {
          MongoRunner.stopMongod(conn);
      }
      
      
      

       

      Show
        /** * JOO wrong results (minor, deprecated BSON undefined): an $expr equi-join treats a BSON * "undefined" value as different from a missing field, while aggregation $eq (classic $lookup and * SBE $lookup without join optimization) treats them as equal. * * The hash join buckets fillEmpty(path, null ): undefined stays undefined and never meets null , and * the index probe seeks undefined while the index stores missing as null . */ 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: undefined}, {_id: 3}])); assert.commandWorked(db.b.insertMany([{_id: 1, y: undefined}, {_id: 3}])); assert.commandWorked(db.a.createIndex({x: 1})); assert.commandWorked(db.b.createIndex({y: 1})); const pipeline = [ {$lookup: {from: "b" , let: {v: "$x" }, pipeline: [{$match: {$expr: {$eq: [ "$y" , "$$v" ]}}}], as: "b" }}, {$unwind: "$b" }, {$project: {_id: 1, bid: "$b._id" }}, ]; 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, 4, tojson(expected)); // assert: [ { "_id" : 1, "bid" : 1 }, { "_id" : 3, "bid" : 3 } ] != [ { "_id" : 1, "bid" : 1 }, { "_id" : 1, "bid" : 3 }, { "_id" : 3, "bid" : 1 }, { "_id" : 3, "bid" : 3 } ] : join optimization returned different results assert.sameMembers(run( true ), expected, "join optimization returned different results" ); } finally { MongoRunner.stopMongod(conn); }  
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      Minor because undefined is a deprecated BSON type.

      Aggregation $eq considers undefined equal to a missing field, and classic/SBE $lookup return the row. The JOO hash join buckets fillEmpty(path, null), so undefined never meets the null produced for missing; the index probe seeks undefined while the index stores missing as null.

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

              Created:
              Updated: