-
Type:
Bug
-
Resolution: Fixed
-
Priority:
Major - P3
-
Affects Version/s: None
-
Component/s: Querying
-
Fully Compatible
-
ALL
-
v5.2, v5.1, v5.0, v4.4, v4.2, v4.0, v3.6
-
Query Optimization 2021-06-28, QO 2021-09-20, QO 2021-10-04, QO 2021-10-18, QO 2021-11-01, QO 2021-11-15, QO 2021-11-29
-
None
-
3
-
None
-
None
-
None
-
None
-
None
-
None
When indexing an array element by position (ex: {"a.0"}) if the value at the indexed position is an array, the index won't be multikey and can effect the result of a query if the index is selected as the winning plan.
db.version() // => 4.4.6 db.foo.drop(); db.foo.insert( { a: [ [ "b" ], [ "c" ] ] }); print(db.foo.count({ "a.0": { $elemMatch: { $in: ["b"] } } })); // => 1 /* "inputStage" : { "stage" : "COLLSCAN", "filter" : { "a.0" : { "$elemMatch" : { "$eq" : "b" } } }, "nReturned" : 1.0, "executionTimeMillisEstimate" : 0.0, "works" : 3.0, "advanced" : 1.0, "needTime" : 1.0, "needYield" : 0.0, "saveState" : 0.0, "restoreState" : 0.0, "isEOF" : 1.0, "direction" : "forward", "docsExamined" : 1.0 } */
Now if the following index is created and the operation run again the results are different:
db.foo.createIndex({ "a.0": 1 })
print(db.foo.count({ "a.0": { $elemMatch: { $in: ["b"] } } }));
// => 0
/*
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"a.0" : {
"$elemMatch" : {
"$eq" : "b"
}
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"a.0" : 1.0
},
"indexName" : "a.0_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"a.0" : [
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2.0,
"direction" : "forward",
"indexBounds" : {
"a.0" : [
"[\"b\", \"b\"]"
]
}
}
}
*/