Details
-
Bug
-
Resolution: Fixed
-
Major - P3
-
4.0.6
-
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
Description
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
|
|
Attachments
Issue Links
- 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
-