MongoDB | 2552533

Info

Insert one object into a collection: db.x.insert({a: {b: {str: "Health", num: 1358, c: {str: "parsing Chicken Handmade Concrete Pizza", "date" : ISODate("2019-03-26T16:54:52.891Z")}}}}) and run a non-matching query db.x.aggregate([{$match: {"a.b.c.str": {$gte: "PNG"}}}, {$match: {$and: [{"a.b.str": {$not: {$lte: "Legacy"}}}]}}, {$project: {"a.b.num": 1}}]) you will get no result. The plan for this query is: "slots" : "$$RESULT=s6 env: { s4 = \"PNG\", s5 = \"Legacy\" }", "stages" : "[2] project [s6 = makeBsonObj(MakeObjSpec([_id, a = MakeObj([b = MakeObj([num], Closed, RetNothing)], Closed, RetNothing)], Closed, RetNothing), s2, false)] [1] filter {(traverseF(s1, lambda(l101.0) { traverseF(getField(move(l101.0), \"b\"), lambda(l102.0) { traverseF(getField(move(l102.0), \"c\"), lambda(l103.0) { traverseF(getField(move(l103.0), \"str\"), lambda(l104.0) { ((move(l104.0) >= s4) ?: false) }, false) }, false) }, false) }, false) && !(traverseF(s1, lambda(l105.0) { traverseF(getField(move(l105.0), \"b\"), lambda(l106.0) { traverseF(getField(move(l106.0), \"str\"), lambda(l107.0) { ((move(l107.0) <= s5) ?: false) }, false) }, false) }, false)))} [1] scan s2 s3 none none none none none none lowPriority [s1 = a] @\"08a693ed-8215-4b68-ab31-1d2da2b3739a\" true false " using s4 and s5 to hold the constants used in the plan. Now add a $project stage and another $match with a single $or inside: db.x.explain().aggregate([{$match: {"a.b.c.str": {$gte: "PNG"}}}, {$match: {$and: [{"a.b.str": {$not: {$lte: "Legacy"}}}]}}, {$project: {"a.b.num": 1}}, {$match: {$or: [{"a.b.c.d.num": {$not: {$eq: NumberDecimal(0)}}}]}}]) The plan will add the "0" constant to the list of global slots as s8: "slots" : "$$RESULT=s6 env: { s4 = \"PNG\", s5 = \"Legacy\", s8 = 0 }", "stages" : "[3] filter {!(traverseF(s7, lambda(l101.0) { traverseF(getField(move(l101.0), \"b\"), lambda(l102.0) { traverseF(getField(move(l102.0), \"c\"), lambda(l103.0) { traverseF(getField(move(l103.0), \"d\"), lambda(l104.0) { traverseF(getField(move(l104.0), \"num\"), lambda(l105.0) { ((move(l105.0) == s8) ?: false) }, false) }, false) }, false) }, false) }, false))} \n[2] project [s7 = getField(s6, \"a\")] \n[2] project [s6 = makeBsonObj(MakeObjSpec([_id, a = MakeObj([b = MakeObj([num], Closed, RetNothing)], Closed, RetNothing)], Closed, RetNothing), s2, false)] \n[1] filter {(traverseF(s1, lambda(l101.0) { traverseF(getField(move(l101.0), \"b\"), lambda(l102.0) { traverseF(getField(move(l102.0), \"c\"), lambda(l103.0) { traverseF(getField(move(l103.0), \"str\"), lambda(l104.0) { ((move(l104.0) >= s4) ?: false) }, false) }, false) }, false) }, false) && !(traverseF(s1, lambda(l105.0) { traverseF(getField(move(l105.0), \"b\"), lambda(l106.0) { traverseF(getField(move(l106.0), \"str\"), lambda(l107.0) { ((move(l107.0) <= s5) ?: false) }, false) }, false) }, false)))} \n[1] scan s2 s3 none none none none none none lowPriority [s1 = a] @\"08a693ed-8215-4b68-ab31-1d2da2b3739a\" true false " When we add another expression to the $or, testing the same a.b.c.d.num field tested before db.x.explain().aggregate([{$match: {"a.b.c.str": {$gte: "PNG"}}}, {$match: {$and: [{"a.b.str": {$not: {$lte: "Legacy"}}}]}}, {$project: {"a.b.num": 1}}, {$match: {$or: [{"a.b.c.d.num": {$not: {$eq: NumberDecimal(0)}}}, {"a.b.c.d.num": {$eq: NumberLong(0)}}]}}]) has the effect of reusing s4 for the operation, and replaces the "PNG" value with the new (reused) "0" value: "slots" : "$$RESULT=s6 env: { s4 = 0, s5 = \"Legacy\", s8 = 0 }", "stages" : "[3] filter {(!(traverseF(s7, lambda(l101.0) { traverseF(getField(move(l101.0), \"b\"), lambda(l102.0) { traverseF(getField(move(l102.0), \"c\"), lambda(l103.0) { traverseF(getField(move(l103.0), \"d\"), lambda(l104.0) { traverseF(getField(move(l104.0), \"num\"), lambda(l105.0) { ((move(l105.0) == s8) ?: false) }, false) }, false) }, false) }, false) }, false)) || traverseF(s7, lambda(l106.0) { traverseF(getField(move(l106.0), \"b\"), lambda(l107.0) { traverseF(getField(move(l107.0), \"c\"), lambda(l108.0) { traverseF(getField(move(l108.0), \"d\"), lambda(l109.0) { traverseF(getField(move(l109.0), \"num\"), lambda(l110.0) { ((move(l110.0) == s4) ?: false) }, false) }, false) }, false) }, false) }, false))} \n[2] project [s7 = getField(s6, \"a\")] [2] project [s6 = makeBsonObj(MakeObjSpec([_id, a = MakeObj([b = MakeObj([num], Closed, RetNothing)], Closed, RetNothing)], Closed, RetNothing), s2, false)] [1] filter {(traverseF(s1, lambda(l101.0) { traverseF(getField(move(l101.0), \"b\"), lambda(l102.0) { traverseF(getField(move(l102.0), \"c\"), lambda(l103.0) { traverseF(getField(move(l103.0), \"str\"), lambda(l104.0) { ((move(l104.0) >= s4) ?: false) }, false) }, false) }, false) }, false) && !(traverseF(s1, lambda(l105.0) { traverseF(getField(move(l105.0), \"b\"), lambda(l106.0) { traverseF(getField(move(l106.0), \"str\"), lambda(l107.0) { ((move(l107.0) <= s5) ?: false) }, false) }, false) }, false)))} [1] scan s2 s3 none none none none none none lowPriority [s1 = a] @\"08a693ed-8215-4b68-ab31-1d2da2b3739a\" true false " Note that if the second test on the a.b.c.d.num field was against a different value, e.g. "1", it would be assigned to a new s9 global slot

Top User Comments

xgen-internal-githook commented on Thu, 14 Mar 2024 22:29:36 +0000: Author: {'name': 'Alberto Massari', 'email': 'alberto.massari@mongodb.com', 'username': 'albymassari'} Message: SERVER-85516 Fix MatchExpression parameterization to assign the correct reused parameter number (#18412) GitOrigin-RevId: a491f4018fa0663316a83e5b5ccc19ab91d1cd74 Branch: v7.4test https://github.com/mongodb/mongo/commit/3c9f8a23e141af256f25e35116cb178c569d4c38 xgen-internal-githook commented on Fri, 26 Jan 2024 20:12:08 +0000: Author: {'name': 'Alberto Massari', 'email': 'alberto.massari@mongodb.com', 'username': 'albymassari'} Message: SERVER-85516 Fix MatchExpression parameterization to assign the correct reused parameter number (#18412) GitOrigin-RevId: a491f4018fa0663316a83e5b5ccc19ab91d1cd74 Branch: v7.3 https://github.com/mongodb/mongo/commit/c8ee3ae3ef9686c8bd870dad271beef9d113f9fc xgen-internal-githook commented on Fri, 26 Jan 2024 09:56:37 +0000: Author: {'name': 'Alberto Massari', 'email': 'alberto.massari@mongodb.com', 'username': 'albymassari'} Message: SERVER-85516 Fix MatchExpression parameterization to assign the correct reused parameter number (#18229) GitOrigin-RevId: 694882b37bb217437d63b672c8a0243540abf80c Branch: master https://github.com/mongodb/mongo/commit/6474d8e47ac5604d273c3bedc53bf1f694c59017

Steps to Reproduce