-
Type:
Bug
-
Resolution: Fixed
-
Priority:
Major - P3
-
Affects Version/s: None
-
Component/s: None
-
Query Optimization
-
Fully Compatible
-
ALL
-
v7.0, v6.3, v6.0, v5.0, v4.4
-
QO 2023-05-15
When dependency analysis sees $lookup it adds "localField" content to fields it needs from the original collection. However, when that field includes a numeric component (which we may or may not intended to support as a numeric index) what happens is we push it into transformBy where it's interpreted as a field called "0" because projection doesn't support numeric references to array positions like queries do.
Create any collection orders and run:
db.orders.explain().aggregate([ { $lookup: { from: "productsxxx", localField: "products.0.productId", foreignField: "_id", as: "firstProduct" } }, {$match:{a:1}}, {$count:"c"}])
{
explainVersion: '1',
stages: [
{
'$cursor': {
queryPlanner: {
namespace: 'test.orders',
indexFilterSet: false,
parsedQuery: { a: { '$eq': 1 } },
queryHash: 'C810717C',
planCacheKey: 'C810717C',
maxIndexedOrSolutionsReached: false,
maxIndexedAndSolutionsReached: false,
maxScansToExplodeReached: false,
winningPlan: {
stage: 'PROJECTION_DEFAULT',
transformBy: { 'products.0.productId': 1, _id: 0 },
inputStage: {
stage: 'COLLSCAN',
filter: { a: { '$eq': 1 } },
direction: 'forward'
}
},
rejectedPlans: []
}
}
},
{
'$lookup': {
from: 'productsxxx',
as: 'firstProduct',
localField: 'products.0.productId',
foreignField: '_id'
}
},
{
'$group': { _id: { '$const': null }, c: { '$sum': { '$const': 1 } } }
},
{ '$project': { c: true, _id: false } }
] // etc
Note transformation {{transformBy: { 'products.0.productId': 1, _id: 0 }}} which will not reach into arrays but rather only match field "0" of "products".