Details
-
Bug
-
Resolution: Fixed
-
Major - P3
-
None
-
None
-
Query Optimization
-
Fully Compatible
-
ALL
-
v7.0, v6.3, v6.0, v5.0, v4.4
-
QO 2023-05-15
Description
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".