Prior to SERVER-117881, when determining if a pipeline is eligible for join reordering, we verified that every $lookup had at least one join predicate (local/foreign or subpipeline) before allowing it to participate in re-ordering. If we discovered a $lookup without such a predicate, we would leave it in the suffix of the pipeline.

After that ticket, we instead permit those $lookups to be translated optimistically in case a later $lookup adds a join predicate connecting that node to the rest of the graph. At the end of translation, if we end up with a connected graph, we may proceed with join reordering, otherwise we fall back. This allows some additional queries to be reordered, including a TPC-H query.

One other implication is that some queries that contain a sequence of lookup-unwind pairs where a prefix forms a connected graph and a suffix contains a cross-product will not be eligible for join reordering. This ticket is to add support for those queries, for example:

[
        // Prefix that is eligible
        {
            $lookup: {
                from: coll12.getName(),
                localField: "a",
                foreignField: "a",
                as: "coll12",
            },
        },
        {$unwind: "$coll12”},
        // Suffix that is not eligible due the to cross-product required
        {
            $lookup: {
                from: coll13.getName(),
                pipeline: [{$match: {a: {$gt: 0}}}],
                as: "coll13",
            },
        },
        {$unwind: "$coll13"},
    ],
 

This is a particularly narrow subset of queries. The subpipeline in the ineligible lookup must have a single $match in it, and the $match must only have single-table predicates.

If the subpipeline is anything else (for example, a join condition that we do not support today, or $sort or $project), then we already support terminating at the $lookup and allowing the prefix to go through join reordering.

If we add support for cross-products, then this ticket will no longer necessary, as the query above would be considered eligible.