-
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
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\"]" ] } } } */