OPERATIONAL DEFECT DATABASE
...
...
BugZero found this defect 2667 days ago.
If an array element is used as the localField for the $lookup aggregation pipe, a $project anywhere else in the pipeline will fail to return results and also take a while to run. There is a workaround by adding a field containing the local element and using that field as the localField: db.venues.aggregate( // Pipeline [ // Stage 1 { $addFields: { "firstChainId": { "$arrayElemAt": [ "$chainIds", 0 ] } } }, // Stage 2 { $lookup: { "from" : "chains", "localField" : "firstChainId", "foreignField" : "_id", "as" : "venueChain" } }, // Stage 3 { $addFields: { "chainName": "$venueChain.name" } }, // Stage 4 { $project: { n: 1, chainName: 1 } }, ], // Options { cursor: { batchSize: 50 } } );
JIRAUSER1261429 commented on Fri, 8 Sep 2023 14:13:54 +0000: The investigation revealed that the ticket had been fixed by SERVER-75101. JIRAUSER1261429 commented on Thu, 7 Sep 2023 17:27:26 +0000: kateryna.kamenieva@mongodb.com, the issue was fixed in SERVER-75101, commit 9f160dea3f2d311efb8f981c239e717b79a09f65 david.storch commented on Mon, 27 Nov 2017 21:19:40 +0000: asya, yeah, it looks pretty closely related. I'd rather keep both tickets open for the time being until we have determined a clear path to fix. As I mentioned above, I can imagine fixing this either by treating this as a bug in projection semantics or as a bug in dependency analysis. asya commented on Thu, 23 Nov 2017 21:25:03 +0000: Is this just an instance of SERVER-26066? asya commented on Thu, 23 Nov 2017 20:50:37 +0000: It looks like there may be other workarounds to the issue. One is if there is an explicit $project before $lookup for the array field db.coll.aggregate([{$project:{x:1}},{$lookup: {from: "coll", as: "as", localField: "x.0", foreignField: "x.0"}}, {$addFields: {z: "$as.x.y"}}, {$project: {z: 1}}]) { "_id" : ObjectId("5a17305db5843981b6727de7"), "z" : [ [ 1, 2 ] ] } david.storch commented on Tue, 24 Oct 2017 14:32:23 +0000: This looks like a bug having to do with aggregation's dependency analysis, which attempts to compute the projection that appears in the explain output as $cursor.fields. The first query that James posted above returns the incorrect results because "x.0" is computed as a dependency. However, this projection causes the query subsystem to incorrectly exclude some of the data required to execute the $lookup: > db.coll.find({}, {"as.x.y": 1, "x.0": 1, "z": 1, "_id": 1}) { "_id" : ObjectId("59ef461bce8432e1c921c516"), "x" : [ { }, { } ] } I can imagine a few ways to fix this: Change the behavior of the "x.0" projection above, since its behavior is arguably a bug. Change the dependency analysis system to avoid pushing projections involving numerical path components down to the $cursor stage. james.wahlin@10gen.com commented on Tue, 24 Oct 2017 13:54:36 +0000: Reopening as I can reproduce this issue locally: > db.coll.insert({x: [{y: 1}, {y: 2}]}) WriteResult({ "nInserted" : 1 }) > db.coll.aggregate([{$lookup: {from: "coll", as: "as", localField: "x.0", foreignField: "x.0"}}, {$addFields: {z: "$as.x.y"}}, {$project: {z: 1}}]) { "_id" : ObjectId("59ef3e7d99f6610e714ff562"), "z" : [ ] } > db.coll.aggregate([{$addFields: {a: {$arrayElemAt: ["$x", 0]}}},{$lookup: {from: "coll", as: "as", localField: "a", foreignField: "x.0"}}, {$addFields: {z: "$as.x.y"}}, {$project: {z: 1}}]) { "_id" : ObjectId("59ef3e7d99f6610e714ff562"), "z" : [ [ 1, 2 ] ] } Below is the explain for both runs: > db.coll.explain().aggregate([{$lookup: {from: "coll", as: "as", localField: "x.0", foreignField: "x.0"}}, {$addFields: {z: "$as.x.y"}}, {$project: {z: 1}}]) { "stages" : [ { "$cursor" : { "query" : { }, "fields" : { "as.x.y" : 1, "x.0" : 1, "z" : 1, "_id" : 1 }, "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.coll", "indexFilterSet" : false, "parsedQuery" : { }, "winningPlan" : { "stage" : "COLLSCAN", "direction" : "forward" }, "rejectedPlans" : [ ] } } }, { "$lookup" : { "from" : "coll", "as" : "as", "localField" : "x.0", "foreignField" : "x.0" } }, { "$addFields" : { "z" : "$as.x.y" } }, { "$project" : { "_id" : true, "z" : true } } ], "ok" : 1 } > > db.coll.explain().aggregate([{$addFields: {a: {$arrayElemAt: ["$x", 0]}}},{$lookup: {from: "coll", as: "as", localField: "a", foreignField: "x.0"}}, {$addFields: {z: "$as.x.y"}}, {$project: {z: 1}}]) { "stages" : [ { "$cursor" : { "query" : { }, "fields" : { "a" : 1, "as.x.y" : 1, "x" : 1, "z" : 1, "_id" : 1 }, "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.coll", "indexFilterSet" : false, "parsedQuery" : { }, "winningPlan" : { "stage" : "COLLSCAN", "direction" : "forward" }, "rejectedPlans" : [ ] } } }, { "$addFields" : { "a" : { "$arrayElemAt" : [ "$x", { "$const" : 0 } ] } } }, { "$lookup" : { "from" : "coll", "as" : "as", "localField" : "a", "foreignField" : "x.0" } }, { "$addFields" : { "z" : "$as.x.y" } }, { "$project" : { "_id" : true, "z" : true } } ], "ok" : 1 } mark.agarunov commented on Fri, 20 Oct 2017 21:18:39 +0000: Hello damian@liveapp.com, We haven’t heard back from you for some time, so I’m going to mark this ticket as resolved. If this is still an issue for you, please provide additional information and we will reopen the ticket. Thanks, Mark thomas.schubert commented on Fri, 29 Sep 2017 18:45:46 +0000: Hi damian@liveapp.com, We still need additional information to diagnose the problem. If this is still an issue for you, would you please provide the output of the commands executed with the explain option? Thank you, Kelsey ramon.fernandez commented on Thu, 14 Sep 2017 20:26:42 +0000: Apologies for the delay in getting back to you damian@liveapp.com. Can you please run both commands with the explain option and send us the output? Thanks, Ramón.
db.venues.aggregate( // Pipeline [ // Stage 1 { $lookup: { "from" : "chains", "localField" : "chainIds.0", "foreignField" : "_id", "as" : "venueChain" } }, // Stage 2 { $addFields: { "chainName": "$venueChain.name" } }, // Stage 3 { $project: { n: 1, chainName: 1 } }, ], // Options { cursor: { batchSize: 50 } } );
