-
Type: Bug
-
Resolution: Fixed
-
Priority: Major - P3
-
Affects Version/s: 4.0.6
-
Component/s: Aggregation Framework
-
Fully Compatible
-
ALL
-
Query 2019-12-30, Query Optimization 2021-02-22, Query Optimization 2021-03-08, Query Optimization 2021-03-22, Query Optimization 2021-04-05
-
(copied to CRM)
-
120
We check if $match has an expression that isn't on "as" and move it before $lookup - if it has an expression on "as.x" we move it into $lookup.
Expected behavior:
db.foo.explain().aggregate({$lookup:{as:"a",from:"b",localField:"x",foreignField:"y"}},{$unwind:"$a"},{$match:{"a.z ":10,x:{$eq:5}}}) { "stages" : [ { "$cursor" : { "query" : { "x" : { "$eq" : 5 } }, "queryPlanner" : { "plannerVersion" : 1, "namespace" : "tpcds10.foo", "indexFilterSet" : false, "parsedQuery" : { "x" : { "$eq" : 5 } }, "winningPlan" : { "stage" : "EOF" }, "rejectedPlans" : [ ] } } }, { "$lookup" : { "from" : "b", "as" : "a", "localField" : "x", "foreignField" : "y", "unwinding" : { "preserveNullAndEmptyArrays" : false }, "matching" : { "z" : { "$eq" : 10 } } } } ], "ok" : 1 }
Using $expr for x:5 breaks the $lookup matching optimization though equality for x:5 still gets moved before $lookup but does not get pushed into cursor:
db.foo.explain().aggregate({$lookup:{as:"a",from:"b",localField:"x",foreignField:"y"}},{$unwind:"$a"},{$match:{"a.z ":10,$expr:{$eq:["$x",5]}}}) { "stages" : [ { "$cursor" : { "query" : { }, "queryPlanner" : { "plannerVersion" : 1, "namespace" : "tpcds10.foo", "indexFilterSet" : false, "parsedQuery" : { }, "winningPlan" : { "stage" : "EOF" }, "rejectedPlans" : [ ] } } }, { "$match" : { "x" : { "$_internalExprEq" : 5 } } }, { "$lookup" : { "from" : "b", "as" : "a", "localField" : "x", "foreignField" : "y", "unwinding" : { "preserveNullAndEmptyArrays" : false } } }, { "$match" : { "$and" : [ { "a.z" : { "$eq" : 10 } }, { "x" : { "$_internalExprEq" : 5 } }, { "$expr" : { "$eq" : [ "$x", { "$const" : 5 } ] } } ] } } ], "ok" : 1
- causes
-
SERVER-66072 $match sampling and $group aggregation strange behavior
- Closed
- is related to
-
SERVER-34926 allow $expr with comparison bounded on both sides with same type to use a non-multikey index
- Closed
-
SERVER-39943 Create match expressions for aggregation $gt/$gte/$lt/$lte and add to $expr rewrite
- Closed