-
Type:
Bug
-
Resolution: Fixed
-
Priority:
Major - P3
-
Affects Version/s: 4.0.6
-
Component/s: Aggregation Framework
-
Labels:
-
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
-
-
- Closed
-
- is related to
-
SERVER-34926 allow $expr with comparison bounded on both sides with same type to use a non-multikey index
-
- Closed
-
-
-
- Closed
-