ExportXMLWordPrintableJSON

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

       

      
      /**
       * JOO wrong results: a localField/foreignField (loose equality) join drops rows whose join key is
       * NaN when the join is executed as a nested loop join or an indexed nested loop join.
       *
       * MQL equality treats NaN as equal to NaN (classic $lookup, SBE $lookup, and the JOO hash join all
       * return the row). The NLJ and INLJ stage builders lower the loose predicate as
       * fillEmpty(l, null) == fillEmpty(r, null), i.e. the SBE "eq" instruction, which uses IEEE
       * semantics and evaluates NaN == NaN to false. The INLJ index probe does find the NaN keys; the
       * residual equality filter then rejects them.
       *
       * The join method is forced here for determinism; with a larger foreign collection the optimizer
       * picks INLJ on its own and the same rows go missing.
       */
      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: NaN}, {_id: 2, x: 1}]));
          assert.commandWorked(db.b.insertMany([{_id: 1, y: NaN}, {_id: 2, 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: "b"}},
              {$unwind: "$b"},
              {$project: {_id: 1, bid: "$b._id"}},
          ];
          assert.commandWorked(db.adminCommand({setParameter: 1, internalEnableJoinOptimization: false}));
          const expected = db.a.aggregate(pipeline).toArray();
          assert.sameMembers(expected, [{_id: 1, bid: 1}, {_id: 2, bid: 2}]);
          assert.commandWorked(db.adminCommand({setParameter: 1, internalEnableJoinOptimization: true}));
          for (const method of ["NLJ", "INLJ"]) {
              assert.commandWorked(db.adminCommand({setParameter: 1, internalJoinMethod: method}));
              // assert: [ { "_id" : 2, "bid" : 2 } ] != [ { "_id" : 1, "bid" : 1 }, { "_id" : 2, "bid" : 2 } ] : NLJ 
              assert.sameMembers(db.a.aggregate(pipeline).toArray(), expected, method);
          }
      } finally {
          MongoRunner.stopMongod(conn);
      }
      
      
      

       

      Show
        /** * JOO wrong results: a localField/foreignField (loose equality) join drops rows whose join key is * NaN when the join is executed as a nested loop join or an indexed nested loop join. * * MQL equality treats NaN as equal to NaN (classic $lookup, SBE $lookup, and the JOO hash join all * return the row). The NLJ and INLJ stage builders lower the loose predicate as * fillEmpty(l, null ) == fillEmpty(r, null ), i.e. the SBE "eq" instruction, which uses IEEE * semantics and evaluates NaN == NaN to false . The INLJ index probe does find the NaN keys; the * residual equality filter then rejects them. * * The join method is forced here for determinism; with a larger foreign collection the optimizer * picks INLJ on its own and the same rows go missing. */ 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: NaN}, {_id: 2, x: 1}])); assert.commandWorked(db.b.insertMany([{_id: 1, y: NaN}, {_id: 2, 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: "b" }}, {$unwind: "$b" }, {$project: {_id: 1, bid: "$b._id" }}, ]; assert.commandWorked(db.adminCommand({setParameter: 1, internalEnableJoinOptimization: false })); const expected = db.a.aggregate(pipeline).toArray(); assert.sameMembers(expected, [{_id: 1, bid: 1}, {_id: 2, bid: 2}]); assert.commandWorked(db.adminCommand({setParameter: 1, internalEnableJoinOptimization: true })); for ( const method of [ "NLJ" , "INLJ" ]) { assert.commandWorked(db.adminCommand({setParameter: 1, internalJoinMethod: method})); // assert: [ { "_id" : 2, "bid" : 2 } ] != [ { "_id" : 1, "bid" : 1 }, { "_id" : 2, "bid" : 2 } ] : NLJ assert.sameMembers(db.a.aggregate(pipeline).toArray(), expected, method); } } finally { MongoRunner.stopMongod(conn); }  
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      The loose join predicate is lowered in gen_lookup.cpp as fillEmpty(l, null) == fillEmpty(r, null) using the SBE eq instruction, which follows IEEE semantics (NaN != NaN). MQL equality treats NaN as equal to NaN; classic $lookup, SBE $lookup and the JOO hash join all return the row. INLJ finds the key via the index but the residual equality filter rejects it.

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

              Created:
              Updated: