Loading...

XML

Word

Printable

JSON

Assigned Teams:

Query Optimization
Operating System:
ALL
Steps To Reproduce:
Hide

const collNumeric = db.collNumeric; collNumeric.drop(); assert.commandWorked(collNumeric.insertOne({a: [1, 2], b: 1})); assert.commandWorked(collNumeric.createIndex({"a.0": 1})); const coll1 = db.coll1; coll1.drop(); assert.commandWorked(coll1.insertOne({a: 1, b: 1, x: {c: 1}})); // This $lookup query joins with one doc: collNumeric.aggregate({$lookup: {from: coll1.getName(), localField: "a.0", foreignField: "a", as: "joined"}}); // But this one joins with 0 docs collNumeric.aggregate({ $lookup: { from: coll1.getName(), let: {aa: "$a.0"}, pipeline: [{$match: {$expr: {$eq: ["$$aa", "$a"]}}}], as: "joined", }, })
Show
const collNumeric = db.collNumeric; collNumeric.drop(); assert.commandWorked(collNumeric.insertOne({a: [1, 2], b: 1})); assert.commandWorked(collNumeric.createIndex({"a.0": 1})); const coll1 = db.coll1; coll1.drop(); assert.commandWorked(coll1.insertOne({a: 1, b: 1, x: {c: 1}})); // This $lookup query joins with one doc: collNumeric.aggregate({$lookup: {from: coll1.getName(), localField: "a.0", foreignField: "a", as: "joined"}}); // But this one joins with 0 docs collNumeric.aggregate({ $lookup: { from: coll1.getName(), let: {aa: "$a.0"}, pipeline: [{$match: {$expr: {$eq: ["$$aa", "$a"]}}}], as: "joined", }, })
CAR Domain/s:
None

Assigning a let variable in a $lookup to a numeric field path appears to set this variable to an incorrect value (see repro steps below).

is related to

SERVER-36681 Dotted path {$ne: null} against array of scalars returns incorrect results

SERVER-117479 Multiple $match stages in subpipeline prevent join optimization