From fc049222717e652b68eeba89dbd11106baf461f9 Mon Sep 17 00:00:00 2001 From: Mihai Andrei Date: Wed, 9 Mar 2022 19:21:59 +0000 Subject: SERVER-62675 Test that $lookup gets pushed down after multiplanning --- jstests/noPassthrough/lookup_pushdown.js | 695 ++++++++++++++++++------------- 1 file changed, 415 insertions(+), 280 deletions(-) (limited to 'jstests/noPassthrough') diff --git a/jstests/noPassthrough/lookup_pushdown.js b/jstests/noPassthrough/lookup_pushdown.js index 0c13dadc16d..58b1dd0b1b3 100644 --- a/jstests/noPassthrough/lookup_pushdown.js +++ b/jstests/noPassthrough/lookup_pushdown.js @@ -7,7 +7,7 @@ "use strict"; load("jstests/libs/sbe_util.js"); // For 'checkSBEEnabled()'. -load("jstests/libs/analyze_plan.js"); // For 'getAggPlanStages()'. +load("jstests/libs/analyze_plan.js"); // For 'getAggPlanStages()' and 'hasRejectedPlans()' const JoinAlgorithm = { Classic: 0, @@ -24,7 +24,12 @@ const name = "lookup_pushdown"; const foreignCollName = "foreign_lookup_pushdown"; const viewName = "view_lookup_pushdown"; -function runTest(coll, pipeline, expectedJoinAlgorithm, aggOptions = {}, errMsgRegex = null) { +function runTest(coll, + pipeline, + expectedJoinAlgorithm, + aggOptions = {}, + errMsgRegex = null, + checkMultiPlanning = false) { const options = Object.assign({pipeline, cursor: {}}, aggOptions); const response = coll.runCommand("aggregate", options); @@ -66,6 +71,12 @@ function runTest(coll, pipeline, expectedJoinAlgorithm, aggOptions = {}, errMsgR assert.eq("NestedLoopJoin", strategy, "Incorrect strategy; expected NestedLoopJoin, got " + tojson(strategy)); + + // Verify that multiplanning took place by verifying that there was at least one rejected + // plan. + if (checkMultiPlanning) { + assert(hasRejectedPlans(explain), explain); + } } } @@ -84,264 +95,385 @@ assert.commandWorked(foreignColl.insert({_id: 0, b: 2, c: 2})); assert.commandWorked(db.createView(viewName, foreignCollName, [{$match: {b: {$gte: 0}}}])); let view = db[viewName]; -// Basic $lookup. -runTest(coll, - [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], - JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); - -// TODO SERVER-64091: Add a test case for pushed down $lookup against a non-existent foreign -// collection. - -// Self join $lookup, no views. -runTest(coll, - [{$lookup: {from: name, localField: "a", foreignField: "a", as: "out"}}], - JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); - -// Self join $lookup; left hand is a view. This is expected to be pushed down because the view -// pipeline itself is a $match, which is eligible for pushdown. -runTest(view, - [{$lookup: {from: name, localField: "a", foreignField: "a", as: "out"}}], - JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); - -// Self join $lookup; right hand is a view. -runTest(coll, - [{$lookup: {from: viewName, localField: "a", foreignField: "a", as: "out"}}], - JoinAlgorithm.Classic /* expectedJoinAlgorithm */); - -// Self join $lookup; both namespaces are views. -runTest(view, - [{$lookup: {from: viewName, localField: "a", foreignField: "a", as: "out"}}], - JoinAlgorithm.Classic /* expectedJoinAlgorithm */); - -// $lookup preceded by $match. -runTest(coll, - [ - {$match: {a: {$gte: 0}}}, - {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}} - ], - JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); - -// $lookup preceded by $project. -runTest(coll, - [ - {$project: {a: 1}}, - {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}} - ], - JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); - -// $lookup preceded by $project which features an SBE-incompatible expression. -// TODO SERVER-51542: Update or remove this test case once $pow is implemented in SBE. -runTest(coll, - [ - {$project: {exp: {$pow: ["$a", 3]}}}, - {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}} - ], - JoinAlgorithm.Classic /* expectedJoinAlgorithm */); - -// $lookup preceded by $group. -runTest(coll, - [ - {$group: {_id: "$a", sum: {$sum: 1}}}, - {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}} - ], - JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); - -// $lookup preceded by $group that is not eligible for pushdown. -// TODO SERVER-51542: Update or remove this test case once $pow is implemented in SBE. -runTest(coll, - [ - {$group: {_id: {$pow: ["$a", 3]}, sum: {$sum: 1}}}, - {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}} - ], - JoinAlgorithm.Classic /* expectedJoinAlgorithm */); - -// Consecutive $lookups, where the first $lookup is against a view. -runTest(coll, - [ - {$lookup: {from: viewName, localField: "a", foreignField: "b", as: "out"}}, - {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}} - ], - JoinAlgorithm.Classic /* expectedJoinAlgorithm */); - -// Consecutive $lookups, where the first $lookup is against a regular collection. Here, neither -// $lookup is eligible for pushdown because currently, we can only know whether any secondary -// collection is a view or a sharded collection. -runTest(coll, - [ - {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}, - {$lookup: {from: viewName, localField: "a", foreignField: "b", as: "out"}} - ], - JoinAlgorithm.Classic /* expectedJoinAlgorithm */); - -// $lookup with pipeline. -runTest(coll, - [{$lookup: {from: foreignCollName, let: {foo: "$b"}, pipeline: [{$match: {$expr: {$eq: ["$$foo", -2]}}}], as: "out"}}], JoinAlgorithm.Classic /* expectedJoinAlgorithm */); - -// $lookup that absorbs $unwind. -runTest(coll, - [ - {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}, - {$unwind: "$out"} - ], - JoinAlgorithm.Classic /* expectedJoinAlgorithm */); - -// $lookup that absorbs $match. -runTest(coll, - [ - {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}, - {$unwind: "$out"}, - {$match: {out: {$gte: 0}}} - ], - JoinAlgorithm.Classic /* expectedJoinAlgorithm */); - -// $lookup that does not absorb $match. -runTest(coll, - [ - {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}, - {$match: {out: {$gte: 0}}} - ], - JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); - -// Run a $lookup with 'allowDiskUse' enabled. Because the foreign collection is very small, we -// should select hash join. -runTest(coll, - [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], - JoinAlgorithm.HJ /* expectedJoinAlgorithm */, - {allowDiskUse: true}); +(function testLookupPushdownBasicCases() { + // Basic $lookup. + runTest(coll, + [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], + JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); + + // TODO SERVER-64091: Add a test case for pushed down $lookup against a non-existent foreign + // collection. + + // Self join $lookup, no views. + runTest(coll, + [{$lookup: {from: name, localField: "a", foreignField: "a", as: "out"}}], + JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); + + // Self join $lookup; left hand is a view. This is expected to be pushed down because the view + // pipeline itself is a $match, which is eligible for pushdown. + runTest(view, + [{$lookup: {from: name, localField: "a", foreignField: "a", as: "out"}}], + JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); + + // Self join $lookup; right hand is a view. + runTest(coll, + [{$lookup: {from: viewName, localField: "a", foreignField: "a", as: "out"}}], + JoinAlgorithm.Classic /* expectedJoinAlgorithm */); + + // Self join $lookup; both namespaces are views. + runTest(view, + [{$lookup: {from: viewName, localField: "a", foreignField: "a", as: "out"}}], + JoinAlgorithm.Classic /* expectedJoinAlgorithm */); + + // $lookup preceded by $match. + runTest(coll, + [ + {$match: {a: {$gte: 0}}}, + {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}} + ], + JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); + + // $lookup preceded by $project. + runTest(coll, + [ + {$project: {a: 1}}, + {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}} + ], + JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); + + // $lookup preceded by $project which features an SBE-incompatible expression. + // TODO SERVER-51542: Update or remove this test case once $pow is implemented in SBE. + runTest(coll, + [ + {$project: {exp: {$pow: ["$a", 3]}}}, + {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}} + ], + JoinAlgorithm.Classic /* expectedJoinAlgorithm */); + + // $lookup preceded by $group. + runTest(coll, + [ + {$group: {_id: "$a", sum: {$sum: 1}}}, + {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}} + ], + JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); + + // $lookup preceded by $group that is not eligible for pushdown. + // TODO SERVER-51542: Update or remove this test case once $pow is implemented in SBE. + runTest(coll, + [ + {$group: {_id: {$pow: ["$a", 3]}, sum: {$sum: 1}}}, + {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}} + ], + JoinAlgorithm.Classic /* expectedJoinAlgorithm */); + + // Consecutive $lookups, where the first $lookup is against a view. + runTest(coll, + [ + {$lookup: {from: viewName, localField: "a", foreignField: "b", as: "out"}}, + {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}} + ], + JoinAlgorithm.Classic /* expectedJoinAlgorithm */); + + // Consecutive $lookups, where the first $lookup is against a regular collection. Here, neither + // $lookup is eligible for pushdown because currently, we can only know whether any secondary + // collection is a view or a sharded collection. + runTest(coll, + [ + {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}, + {$lookup: {from: viewName, localField: "a", foreignField: "b", as: "out"}} + ], + JoinAlgorithm.Classic /* expectedJoinAlgorithm */); + + // $lookup with pipeline. + runTest(coll, + [{ + $lookup: { + from: foreignCollName, let: {foo: "$b"}, pipeline: [{ + $match: { + $expr: { + $eq: ["$$foo", + 2] + } + } + }], as: "out" + } + }], JoinAlgorithm.Classic /* expectedJoinAlgorithm */); + + // $lookup that absorbs $unwind. + runTest(coll, + [ + {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}, + {$unwind: "$out"} + ], + JoinAlgorithm.Classic /* expectedJoinAlgorithm */); + + // $lookup that absorbs $match. + runTest(coll, + [ + {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}, + {$unwind: "$out"}, + {$match: {out: {$gte: 0}}} + ], + JoinAlgorithm.Classic /* expectedJoinAlgorithm */); + + // $lookup that does not absorb $match. + runTest(coll, + [ + {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}, + {$match: {out: {$gte: 0}}} + ], + JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); + + // Run a $lookup with 'allowDiskUse' enabled. Because the foreign collection is very small, we + // should select hash join. + runTest(coll, + [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], + JoinAlgorithm.HJ /* expectedJoinAlgorithm */, + {allowDiskUse: true}); +}()); // Build an index on the foreign collection that matches the foreignField. This should cause us // to choose an indexed nested loop join. -assert.commandWorked(foreignColl.createIndex({b: 1})); -runTest(coll, - [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], - JoinAlgorithm.INLJ /* expectedJoinAlgorithm */, - {} /* aggOptions */, - /\(b_1, \)$//* errMsgRegex */); +(function testIndexNestedLoopJoinRegularIndex() { + assert.commandWorked(foreignColl.dropIndexes()); + assert.commandWorked(foreignColl.createIndex({b: 1})); + runTest(coll, + [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], + JoinAlgorithm.INLJ /* expectedJoinAlgorithm */, + {} /* aggOptions */, + /\(b_1, \)$//* errMsgRegex */); + assert.commandWorked(foreignColl.dropIndexes()); +})(); // Build a hashed index on the foreign collection that matches the foreignField. Indexed nested loop // join strategy should be used. -assert.commandWorked(foreignColl.dropIndexes()); -assert.commandWorked(foreignColl.createIndex({b: 'hashed'})); -runTest(coll, - [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], - JoinAlgorithm.INLJ /* expectedJoinAlgorithm */, - {} /* aggOptions */, - /\(b_hashed, \)$//* errMsgRegex */); +(function testIndexNestedLoopJoinHashedIndex() { + assert.commandWorked(foreignColl.dropIndexes()); + assert.commandWorked(foreignColl.createIndex({b: 'hashed'})); + runTest(coll, + [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], + JoinAlgorithm.INLJ /* expectedJoinAlgorithm */, + {} /* aggOptions */, + /\(b_hashed, \)$//* errMsgRegex */); + assert.commandWorked(foreignColl.dropIndexes()); +})(); // Build a wildcard index on the foreign collection that matches the foreignField. Nested loop join // strategy should be used. -assert.commandWorked(foreignColl.dropIndexes()); -assert.commandWorked(foreignColl.createIndex({'$**': 1})); -runTest(coll, - [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], - JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); +(function testWildcardIndexInhibitsIndexNestedLoopJoin() { + assert.commandWorked(foreignColl.dropIndexes()); + assert.commandWorked(foreignColl.createIndex({'$**': 1})); + runTest(coll, + [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], + JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); + assert.commandWorked(foreignColl.dropIndexes()); +})(); // Build a compound index that is prefixed with the foreignField. We should use an indexed // nested loop join. -assert.commandWorked(foreignColl.dropIndexes()); -assert.commandWorked(foreignColl.createIndex({b: 1, c: 1, a: 1})); -runTest(coll, - [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], - JoinAlgorithm.INLJ /* expectedJoinAlgorithm */, - {} /* aggOptions */, - /\(b_1_c_1_a_1, \)$//* errMsgRegex */); +(function testCompoundIndexWithForeignFieldPrefix() { + assert.commandWorked(foreignColl.dropIndexes()); + assert.commandWorked(foreignColl.createIndex({b: 1, c: 1, a: 1})); + runTest(coll, + [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], + JoinAlgorithm.INLJ /* expectedJoinAlgorithm */, + {} /* aggOptions */, + /\(b_1_c_1_a_1, \)$//* errMsgRegex */); + assert.commandWorked(foreignColl.dropIndexes()); +})(); // Build multiple compound indexes prefixed with the foreignField. We should utilize the index with // the least amount of components. -assert.commandWorked(foreignColl.dropIndexes()); -assert.commandWorked(foreignColl.createIndex({b: 1, a: 1})); -assert.commandWorked(foreignColl.createIndex({b: 1, c: 1, a: 1})); -runTest(coll, - [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], - JoinAlgorithm.INLJ /* expectedJoinAlgorithm */, - {} /* aggOptions */, - /\(b_1_a_1, \)$//* errMsgRegex */); - -// In the presence of hashed and BTree indexes with the same number of components, we should select -// BTree one. -assert.commandWorked(foreignColl.dropIndexes()); -assert.commandWorked(foreignColl.createIndex({b: 1})); -assert.commandWorked(foreignColl.createIndex({b: 'hashed'})); -runTest(coll, - [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], - JoinAlgorithm.INLJ /* expectedJoinAlgorithm */, - {} /* aggOptions */, - /\(b_1, \)$//* errMsgRegex */); - -// While selecting a BTree index is more preferable, we should favor hashed index if it has smaller -// number of components. -assert.commandWorked(foreignColl.dropIndexes()); -assert.commandWorked(foreignColl.createIndex({b: 1, c: 1, d: 1})); -assert.commandWorked(foreignColl.createIndex({b: 'hashed'})); -runTest(coll, - [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], - JoinAlgorithm.INLJ /* expectedJoinAlgorithm */, - {} /* aggOptions */, - /\(b_hashed, \)$//* errMsgRegex */); +(function testIndexWithFewestComponentsIsUsed() { + assert.commandWorked(foreignColl.dropIndexes()); + assert.commandWorked(foreignColl.createIndex({b: 1, a: 1})); + assert.commandWorked(foreignColl.createIndex({b: 1, c: 1, a: 1})); + runTest(coll, + [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], + JoinAlgorithm.INLJ /* expectedJoinAlgorithm */, + {} /* aggOptions */, + /\(b_1_a_1, \)$//* errMsgRegex */); + assert.commandWorked(foreignColl.dropIndexes()); +}()); + +(function testBTreeIndexChosenOverHashedIndex() { + // In the presence of hashed and BTree indexes with the same number of components, we should + // select BTree one. + assert.commandWorked(foreignColl.dropIndexes()); + assert.commandWorked(foreignColl.createIndex({b: 1})); + assert.commandWorked(foreignColl.createIndex({b: 'hashed'})); + runTest(coll, + [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], + JoinAlgorithm.INLJ /* expectedJoinAlgorithm */, + {} /* aggOptions */, + /\(b_1, \)$//* errMsgRegex */); + assert.commandWorked(foreignColl.dropIndexes()); +}()); + +// While selecting a BTree index is more preferable, we should favor hashed index if it has +// smaller number of components. +(function testFewerComponentsFavoredOverIndexType() { + assert.commandWorked(foreignColl.dropIndexes()); + assert.commandWorked(foreignColl.createIndex({b: 1, c: 1, d: 1})); + assert.commandWorked(foreignColl.createIndex({b: 'hashed'})); + runTest(coll, + [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], + JoinAlgorithm.INLJ /* expectedJoinAlgorithm */, + {} /* aggOptions */, + /\(b_hashed, \)$//* errMsgRegex */); + assert.commandWorked(foreignColl.dropIndexes()); +}()); // If we have two indexes of the same type with the same number of components, index keypattern // should be used as a tie breaker. -assert.commandWorked(foreignColl.dropIndexes()); -assert.commandWorked(foreignColl.createIndex({b: 1, c: 1})); -assert.commandWorked(foreignColl.createIndex({b: 1, a: 1})); -runTest(coll, - [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], - JoinAlgorithm.INLJ /* expectedJoinAlgorithm */, - {} /* aggOptions */, - /\(b_1_a_1, \)$//* errMsgRegex */); +(function testIndexKeyPatternUsedAsTieBreaker() { + assert.commandWorked(foreignColl.dropIndexes()); + assert.commandWorked(foreignColl.createIndex({b: 1, c: 1})); + assert.commandWorked(foreignColl.createIndex({b: 1, a: 1})); + runTest(coll, + [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], + JoinAlgorithm.INLJ /* expectedJoinAlgorithm */, + {} /* aggOptions */, + /\(b_1_a_1, \)$//* errMsgRegex */); + assert.commandWorked(foreignColl.dropIndexes()); +}()); // Build a 2d index on the foreign collection that matches the foreignField. In this case, we should // use regular nested loop join. -assert.commandWorked(foreignColl.dropIndexes()); -assert.commandWorked(foreignColl.createIndex({b: '2d'})); -runTest(coll, - [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], - JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); +(function testNonBTreeOrHashedIndexesNotUsedForPushdown() { + assert.commandWorked(foreignColl.dropIndexes()); + assert.commandWorked(foreignColl.createIndex({b: '2d'})); + runTest(coll, + [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], + JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); + assert.commandWorked(foreignColl.dropIndexes()); +}()); // Build a compound index containing the foreignField, but not as the first field. In this case, // we should use regular nested loop join. -assert.commandWorked(foreignColl.dropIndexes()); -assert.commandWorked(foreignColl.createIndex({a: 1, b: 1, c: 1})); -runTest(coll, - [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], - JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); +(function testForeignFieldNotPrefixInhibitsIndexNestedLoopJoin() { + assert.commandWorked(foreignColl.dropIndexes()); + assert.commandWorked(foreignColl.createIndex({a: 1, b: 1, c: 1})); + runTest(coll, + [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], + JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); + assert.commandWorked(foreignColl.dropIndexes()); +}()); // Multiple $lookup stages in a pipeline that should pick different physical joins. -assert.commandWorked(foreignColl.dropIndexes()); -assert.commandWorked(foreignColl.createIndex({b: 1})); -runTest(coll, - [ - {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "b_out"}}, - {$lookup: {from: foreignCollName, localField: "a", foreignField: "c", as: "c_out"}} - ], - JoinAlgorithm.INLJ /* expectedJoinAlgorithm; The stage with foreignField 'c' will be - built first and use NLJ with no error while the stage with foreignField 'b' will use - INLJ and throw an error */); -runTest( - coll, - [ +( + function + testMultipleLookupStagesPickDifferentPhysicalJoins() { + assert.commandWorked(foreignColl.dropIndexes()); + assert.commandWorked(foreignColl.createIndex({b: 1})); + + // The stage with foreignField 'c' will be built first and use NLJ with no error while + // the stage with foreignField 'b' will use INLJ and throw an error. + runTest( + coll, + [ + { + $lookup: + {from: foreignCollName, localField: "a", foreignField: "b", as: "b_out"} + }, + { + $lookup: + {from: foreignCollName, localField: "a", foreignField: "c", as: "c_out"} + } + ], + JoinAlgorithm.INLJ /* expectedJoinAlgorithm */); + + runTest(coll, [ {$lookup: {from: foreignCollName, localField: "a", foreignField: "c", as: "c_out"}}, {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "b_out"}} - ], - JoinAlgorithm.INLJ /* expectedJoinAlgorithm for the second stage, because it's built first */); - -// "localField" contains a numeric component (unsupported by SBE). -runTest(coll, - [{$lookup: {from: name, localField: "a.0", foreignField: "a", as: "out"}}], - JoinAlgorithm.Classic /* expectedJoinAlgorithm */); - -// "foreignField" contains a numeric component (unsupported by SBE). -runTest(coll, - [{$lookup: {from: name, localField: "a", foreignField: "a.0", as: "out"}}], - JoinAlgorithm.Classic /* expectedJoinAlgorithm */); - -// "as" field contains a numeric component (numbers in this field are treated as literal field names -// so this is supported by SBE). -runTest(coll, - [{$lookup: {from: name, localField: "a", foreignField: "a", as: "out.0"}}], - JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); + ], JoinAlgorithm.INLJ /* expectedJoinAlgorithm for the second stage, because it's built first */); + assert.commandWorked(foreignColl.dropIndexes()); + })(); + +(function testNumericComponentsBehaviorForPushdown() { + // "localField" contains a numeric component (unsupported by SBE). + runTest(coll, + [{$lookup: {from: name, localField: "a.0", foreignField: "a", as: "out"}}], + JoinAlgorithm.Classic /* expectedJoinAlgorithm */); + + // "foreignField" contains a numeric component (unsupported by SBE). + runTest(coll, + [{$lookup: {from: name, localField: "a", foreignField: "a.0", as: "out"}}], + JoinAlgorithm.Classic /* expectedJoinAlgorithm */); + + // "as" field contains a numeric component (numbers in this field are treated as literal field + // names so this is supported by SBE). + runTest(coll, + [{$lookup: {from: name, localField: "a", foreignField: "a", as: "out.0"}}], + JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); +}()); +// Verify that $lookup pushdown works correctly in the presence of multi-planning. +( + function testLookupPushdownWorksWithMultiplanning() { + assert.commandWorked(coll.dropIndexes()); + assert.commandWorked(coll.createIndexes([{a: 1, b: 1}, {a: 1, c: 1}])); + + // Verify that $lookup still gets pushed down when the pipeline prefix is pushed down and + // undergoes multi-planning. + runTest( + coll, + [ + {$match: {a: {$gt: 1}}}, + {$lookup: {from: foreignCollName, localField: "a", foreignField: "c", as: "c_out"}} + ], + JoinAlgorithm.NLJ, /* expectedJoinAlgorithm */ + {}, /* aggOptions */ + null, /* errMsgRegex */ + true /* checkMultiplanning */); + + // Verify that multiple $lookups will still get pushed down when the pipeline prefix is + // pushed down and undergoes multi-planning. + // TODO SERVER-63572 Update this test case to check multiplanning once INLJ runs end to end. + runTest( + coll, + [ + {$match: {a: {$gt: 1}}}, + {$lookup: {from: foreignCollName, localField: "a", foreignField: "c", as: "c_out"}}, + {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "b_out"}} + ], + JoinAlgorithm.NLJ + /* expectedJoinAlgorithm for the second stage, because it's built first */); + + // Verify that $lookup and $group both get pushed down in the presence of multiplanning. + runTest( + coll, + [ + {$match: {a: {$gt: 1}}}, + {$group: {_id: "$a", groupOut: {$sum: 1}}}, + { + $lookup: { + from: foreignCollName, + localField: "groupOut", + foreignField: "c", + as: "c_out" + } + } + ], + JoinAlgorithm.NLJ, /* expectedJoinAlgorithm */ + {}, /* aggOptions */ + null, /* errMsgRegex */ + true /* checkMultiplanning */); + + runTest( + coll, + [ + {$match: {a: {$gt: 1}}}, + {$lookup: {from: foreignCollName, localField: "a", foreignField: "c", as: "c_out"}}, + {$group: {_id: "$c_out", groupOut: {$sum: 1}}}, + ], + JoinAlgorithm.NLJ, /* expectedJoinAlgorithm */ + {}, /* aggOptions */ + null, /* errMsgRegex */ + true /* checkMultiplanning */); + assert.commandWorked(coll.dropIndexes()); + })(); MongoRunner.stopMongod(conn); (function testHashJoinQueryKnobs() { @@ -468,54 +600,57 @@ assert.commandWorked(foreignColl.insert({b: 5})); assert.commandWorked(db.createView(viewName, foreignCollName, [{$match: {b: {$gte: 0}}}])); assert.commandWorked(db.createView(shardedViewName, name, [{$match: {b: {$gte: 0}}}])); -// Both collections are unsharded. -runTest(foreignColl, - [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], - JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); - -// Sharded main collection, unsharded right side. This is not expected to be eligible for pushdown -// because the $lookup will be preceded by a $mergeCursors stage on the merging shard. -runTest(coll, - [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], - JoinAlgorithm.Classic /* expectedJoinAlgorithm */); - -// Sharded main collection, unsharded right side. Here, we are targeting a single shard, so there -// will be no leading $mergeCursors stage. We should still avoid pushing down $lookup. -const singleShardPipeline = [ - {$match: {shardKey: 1}}, - {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}} -]; -runTest(coll, singleShardPipeline, JoinAlgorithm.Classic /* expectedJoinAlgorithm */); - -// Verify that the above pipeline targets a single shard and doesn't use a $mergeCursors stage. -const singleShardExplain = coll.explain().aggregate(singleShardPipeline); -assert(!aggPlanHasStage( - singleShardExplain, "$mergeCursors", "found $mergeCursors in " + tojson(singleShardExplain))); -assert(singleShardExplain.hasOwnProperty("shards"), - "should have shards property in explain: " + tojson(singleShardExplain)); -assert.eq(Object.keys(singleShardExplain["shards"]).length, - 1, - "sharded explain should only" + - " target one shard " + tojson(singleShardExplain)); - -// Both collections are sharded. -runTest(coll, - [{$lookup: {from: name, localField: "a", foreignField: "b", as: "out"}}], - JoinAlgorithm.Classic /* expectedJoinAlgorithm */); - -// Unsharded main collection, sharded right side. -runTest(foreignColl, - [{$lookup: {from: name, localField: "a", foreignField: "b", as: "out"}}], - JoinAlgorithm.Classic /* expectedJoinAlgorithm */); - -// Unsharded main collection, unsharded view right side. -runTest(foreignColl, - [{$lookup: {from: viewName, localField: "a", foreignField: "b", as: "out"}}], - JoinAlgorithm.Classic /* expectedJoinAlgorithm */); - -// Unsharded main collection, sharded view on the right side. -runTest(foreignColl, - [{$lookup: {from: shardedViewName, localField: "a", foreignField: "b", as: "out"}}], - JoinAlgorithm.Classic /* expectedJoinAlgorithm */); +(function testLookupPushdownAgainstShardedCluster() { + // Both collections are unsharded. + runTest(foreignColl, + [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], + JoinAlgorithm.NLJ /* expectedJoinAlgorithm */); + + // Sharded main collection, unsharded right side. This is not expected to be eligible for + // pushdown because the $lookup will be preceded by a $mergeCursors stage on the merging shard. + runTest(coll, + [{$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}}], + JoinAlgorithm.Classic /* expectedJoinAlgorithm */); + + // Sharded main collection, unsharded right side. Here, we are targeting a single shard, so + // there will be no leading $mergeCursors stage. We should still avoid pushing down $lookup. + const singleShardPipeline = [ + {$match: {shardKey: 1}}, + {$lookup: {from: foreignCollName, localField: "a", foreignField: "b", as: "out"}} + ]; + runTest(coll, singleShardPipeline, JoinAlgorithm.Classic /* expectedJoinAlgorithm */); + + // Verify that the above pipeline targets a single shard and doesn't use a $mergeCursors stage. + const singleShardExplain = coll.explain().aggregate(singleShardPipeline); + assert(!aggPlanHasStage(singleShardExplain, + "$mergeCursors", + "found $mergeCursors in " + tojson(singleShardExplain))); + assert(singleShardExplain.hasOwnProperty("shards"), + "should have shards property in explain: " + tojson(singleShardExplain)); + assert.eq(Object.keys(singleShardExplain["shards"]).length, + 1, + "sharded explain should only" + + " target one shard " + tojson(singleShardExplain)); + + // Both collections are sharded. + runTest(coll, + [{$lookup: {from: name, localField: "a", foreignField: "b", as: "out"}}], + JoinAlgorithm.Classic /* expectedJoinAlgorithm */); + + // Unsharded main collection, sharded right side. + runTest(foreignColl, + [{$lookup: {from: name, localField: "a", foreignField: "b", as: "out"}}], + JoinAlgorithm.Classic /* expectedJoinAlgorithm */); + + // Unsharded main collection, unsharded view right side. + runTest(foreignColl, + [{$lookup: {from: viewName, localField: "a", foreignField: "b", as: "out"}}], + JoinAlgorithm.Classic /* expectedJoinAlgorithm */); + + // Unsharded main collection, sharded view on the right side. + runTest(foreignColl, + [{$lookup: {from: shardedViewName, localField: "a", foreignField: "b", as: "out"}}], + JoinAlgorithm.Classic /* expectedJoinAlgorithm */); +}()); st.stop(); }()); -- cgit v1.2.1