Details
-
Bug
-
Resolution: Works as Designed
-
Major - P3
-
None
-
3.6.3, 4.0.1
-
None
-
ALL
-
Hide
Start with empty collection and the index:
> db.comment.drop()true> db.comment.createIndex({ 'links.id': 1 })
{"createdCollectionAutomatically" : true,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}Initially, the query strategy indicates it will use the correct index
> db.comment.find({$expr:{$eq:['$links.id', 'some-string']}}).explain()
{"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.comment",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{"$expr" : {
"$eq" : [
"$links.id",
{"$const" : "some-string"
}]}},{"links.id" : {
"$_internalExprEq" : "some-string"
}}]},"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"$expr" : {
"$eq" : [
"$links.id",
{"$const" : "some-string"
}]}},"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"links.id" : 1
},"indexName" : "links.id_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"links.id" : [ ]
},"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"links.id" : [
"[\"some-string\", \"some-string\"]"
]}}},"rejectedPlans" : [ ]
},"serverInfo" : {
"host" : "caa1852655c1",
"port" : 27017,
"version" : "3.6.3",
"gitVersion" : "9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},"ok" : 1
}But when an element is inserted, it "discovers" that its a multi-key index
> db.comment.insert({ links: [{ id: 'some-string', type: 'test' }] })
WriteResult({ "nInserted" : 1 })
and the query no longer uses the correct index
> db.comment.find({$expr:{$eq:['$links.id', 'some-string']}}).explain()
{"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.comment",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{"$expr" : {
"$eq" : [
"$links.id",
{"$const" : "some-string"
}]}},{"links.id" : {
"$_internalExprEq" : "some-string"
}}]},"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"$and" : [
{"$expr" : {
"$eq" : [
"$links.id",
{"$const" : "some-string"
}]}},{"links.id" : {
"$_internalExprEq" : "some-string"
}}]},"direction" : "forward"
},"rejectedPlans" : [ ]
},"serverInfo" : {
"host" : "caa1852655c1",
"port" : 27017,
"version" : "3.6.3",
"gitVersion" : "9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},"ok" : 1
}ShowStart with empty collection and the index: > db.comment.drop() true > db.comment.createIndex({ 'links.id' : 1 }) { "createdCollectionAutomatically" : true , "numIndexesBefore" : 1 , "numIndexesAfter" : 2 , "ok" : 1 } Initially, the query strategy indicates it will use the correct index > db.comment.find({$expr:{$eq:[ '$links.id' , 'some-string' ]}}).explain() { "queryPlanner" : { "plannerVersion" : 1 , "namespace" : "test.comment" , "indexFilterSet" : false , "parsedQuery" : { "$and" : [ { "$expr" : { "$eq" : [ "$links.id" , { "$const" : "some-string" } ] } }, { "links.id" : { "$_internalExprEq" : "some-string" } } ] }, "winningPlan" : { "stage" : "FETCH" , "filter" : { "$expr" : { "$eq" : [ "$links.id" , { "$const" : "some-string" } ] } }, "inputStage" : { "stage" : "IXSCAN" , "keyPattern" : { "links.id" : 1 }, "indexName" : "links.id_1" , "isMultiKey" : false , "multiKeyPaths" : { "links.id" : [ ] }, "isUnique" : false , "isSparse" : false , "isPartial" : false , "indexVersion" : 2 , "direction" : "forward" , "indexBounds" : { "links.id" : [ "[\"some-string\", \"some-string\"]" ] } } }, "rejectedPlans" : [ ] }, "serverInfo" : { "host" : "caa1852655c1" , "port" : 27017 , "version" : "3.6.3" , "gitVersion" : "9586e557d54ef70f9ca4b43c26892cd55257e1a5" }, "ok" : 1 } But when an element is inserted, it "discovers" that its a multi-key index > db.comment.insert({ links: [{ id: 'some-string' , type: 'test' }] }) WriteResult({ "nInserted" : 1 }) and the query no longer uses the correct index > db.comment.find({$expr:{$eq:[ '$links.id' , 'some-string' ]}}).explain() { "queryPlanner" : { "plannerVersion" : 1 , "namespace" : "test.comment" , "indexFilterSet" : false , "parsedQuery" : { "$and" : [ { "$expr" : { "$eq" : [ "$links.id" , { "$const" : "some-string" } ] } }, { "links.id" : { "$_internalExprEq" : "some-string" } } ] }, "winningPlan" : { "stage" : "COLLSCAN" , "filter" : { "$and" : [ { "$expr" : { "$eq" : [ "$links.id" , { "$const" : "some-string" } ] } }, { "links.id" : { "$_internalExprEq" : "some-string" } } ] }, "direction" : "forward" }, "rejectedPlans" : [ ] }, "serverInfo" : { "host" : "caa1852655c1" , "port" : 27017 , "version" : "3.6.3" , "gitVersion" : "9586e557d54ef70f9ca4b43c26892cd55257e1a5" }, "ok" : 1 }
Description
An $expr expression in a query will not use a multi-key index. Ticket SERVER-31760 indicates this was fixed in 3.6.3 but still fails to work if the index is a multi-key index. This manifests only after an element is inserted that makes the index a multi-key index.
A normal query will use the correct index, but, as with the motivation for SERVER-31760, it is desired to work within a $lookup pipeline where $expr would be required.
The problem exists in 3.6.3 as well as 4.0.1