ExportXMLWordPrintableJSON

    • Query Optimization
    • Fully Compatible
    • ALL
    • Hide

       

      /**
       * JOO wrong results: an $expr equality to null is propagated as a single-table predicate across
       * a localField/foreignField (loose equality) join edge, keeping its strict $expr semantics.
       *
       * {$expr: {$eq: ["$x", null]}} on the base collection is inferred onto the foreign node as
       * {$expr: {$eq: ["$y", null]}}. The join itself uses loose equality (null == missing), so foreign
       * documents with a *missing* y should still join base documents with x: null, but the inferred
       * predicate filters them out before the join. The same happens in the other direction when the
       * $expr null predicate sits in the $lookup sub-pipeline.
       */
      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: null}, {_id: 2}]));
          assert.commandWorked(db.b.insertMany([{_id: 1, y: null}, {_id: 2}]));
          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();
          }
      
          // $expr null predicate on the base collection, propagated to the foreign collection.
          const prefixPipeline = [
              {$match: {$expr: {$eq: ["$x", null]}}},
              {$lookup: {from: "b", localField: "x", foreignField: "y", as: "b"}},
              {$unwind: "$b"},
          ];
          let expected = run(prefixPipeline, false);
          assert.eq(expected.length, 2, tojson(expected));  // {x: null} joins {y: null} and {} (missing y).
          // assert: [ { "_id" : 1, "x" : null, "b" : { "_id" : 1, "y" : null } } ] != [ { "_id" : 1, "x" : null, "b" : { "_id" : 1, "y" : null } }, { "_id" : 1, "x" : null, "b" : { "_id" : 2 } } ] : prefix $expr null predicate
          assert.sameMembers(run(prefixPipeline, true), expected, "prefix $expr null predicate");
      
          // $expr null predicate in the sub-pipeline, propagated to the base collection.
          const subPipeline = [
              {
                  $lookup: {
                      from: "b",
                      localField: "x",
                      foreignField: "y",
                      pipeline: [{$match: {$expr: {$eq: ["$y", null]}}}],
                      as: "b",
                  },
              },
              {$unwind: "$b"},
          ];
          expected = run(subPipeline, false);
          assert.eq(expected.length, 2, tojson(expected));  // {y: null} joins {x: null} and {} (missing x).
          assert.sameMembers(run(subPipeline, true), expected, "sub-pipeline $expr null predicate");
      } finally {
          MongoRunner.stopMongod(conn);
      }
      
      

       

      Show
        /** * JOO wrong results: an $expr equality to null is propagated as a single-table predicate across * a localField/foreignField (loose equality) join edge, keeping its strict $expr semantics. * * {$expr: {$eq: [ "$x" , null ]}} on the base collection is inferred onto the foreign node as * {$expr: {$eq: [ "$y" , null ]}}. The join itself uses loose equality ( null == missing), so foreign * documents with a *missing* y should still join base documents with x: null , but the inferred * predicate filters them out before the join. The same happens in the other direction when the * $expr null predicate sits in the $lookup sub-pipeline. */ 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: null }, {_id: 2}])); assert .commandWorked(db.b.insertMany([{_id: 1, y: null }, {_id: 2}])); 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(); } // $expr null predicate on the base collection, propagated to the foreign collection. const prefixPipeline = [ {$match: {$expr: {$eq: [ "$x" , null ]}}}, {$lookup: {from: "b" , localField: "x" , foreignField: "y" , as: "b" }}, {$unwind: "$b" }, ]; let expected = run(prefixPipeline, false ); assert .eq(expected.length, 2, tojson(expected)); // {x: null } joins {y: null } and {} (missing y). // assert : [ { "_id" : 1, "x" : null , "b" : { "_id" : 1, "y" : null } } ] != [ { "_id" : 1, "x" : null , "b" : { "_id" : 1, "y" : null } }, { "_id" : 1, "x" : null , "b" : { "_id" : 2 } } ] : prefix $expr null predicate assert .sameMembers(run(prefixPipeline, true ), expected, "prefix $expr null predicate" ); // $expr null predicate in the sub-pipeline, propagated to the base collection. const subPipeline = [ { $lookup: { from: "b" , localField: "x" , foreignField: "y" , pipeline: [{$match: {$expr: {$eq: [ "$y" , null ]}}}], as: "b" , }, }, {$unwind: "$b" }, ]; expected = run(subPipeline, false ); assert .eq(expected.length, 2, tojson(expected)); // {y: null } joins {x: null } and {} (missing x). assert .sameMembers(run(subPipeline, true ), expected, "sub-pipeline $expr null predicate" ); } finally { MongoRunner.stopMongod(conn); }  
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      {$expr: {$eq: ["$x", null]}} on one side of a loose (null == missing) join is inferred onto the other node as {$expr: {$eq: ["$y", null]}} by predicate_inferer.cpp. The inferred predicate rejects documents with a missing field that the loose join would have matched.

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

              Created:
              Updated:
              Resolved: