Details
-
Bug
-
Status: Closed
-
Major - P3
-
Resolution: Fixed
-
None
-
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
Description
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\"]"
|
]
|
}
|
}
|
}
|
*/ |