-
Type:
Bug
-
Resolution: Done
-
Priority:
Major - P3
-
None
-
Affects Version/s: 7.0.2
-
Component/s: None
-
None
-
ALL
-
We have a collection 'plain_time_series'. The typical documents in this collection looks like this:
{
"_id" : ObjectId("6565b44aad131a54a85cc60c"),
"name" : "EU-taxonomy Aligned % (SFDR)",
"companyId" : ObjectId("65577ce17124dd000199e6d9"),
"attributes" : [],
"expression" : null
}
The following query returns 0 records, but should return 4:
db.getCollection('plain_time_series').find({ $or: [ { $and: [ { name: { $regex: /^\s*EU-taxonomy Aligned % \(SFDR\)\s*$/i } }, { companyId: { $in: [ ObjectId("65577ce17124dd000199e6d9"), ObjectId("622212e633075400010c9977"), ObjectId("62811ba5541e3600019730d0"), ObjectId("6222112f21573e00018ef332") ] } } ] }, { $and: [ { name: { $regex: /^\s*EU-Taxonomy Aligned \(SFDR\)\s*$/i } }, { companyId: { $in: [ ObjectId("65577ce17124dd000199e6d9"), ObjectId("622212e633075400010c9977"), ObjectId("62811ba5541e3600019730d0"), ObjectId("6222112f21573e00018ef332") ] } } ] }, { $and: [ { name: { $regex: /^\s*EU-taxonomy Eligible % \(SFDR\)\s*$/i } }, { companyId: { $in: [ ObjectId("65577ce17124dd000199e6d9"), ObjectId("622212e633075400010c9977"), ObjectId("62811ba5541e3600019730d0"), ObjectId("6222112f21573e00018ef332") ] } } ] }, { $and: [ { name: { $regex: /^\s*EU-Taxonomy Eligible \(SFDR\)\s*$/i } }, { companyId: { $in: [ ObjectId("65577ce17124dd000199e6d9"), ObjectId("622212e633075400010c9977"), ObjectId("62811ba5541e3600019730d0"), ObjectId("6222112f21573e00018ef332") ] } } ] } ] })
The other similar queries sometimes returns expected result and sometimes not.
Adding {{.limit(<any number>) }}seems to solve the issue.
Below you can find the winning query plan.
{
"stage" : "FETCH",
"planNodeId" : 2,
"filter" : {
"$or" : [
{
"name" : {
"$in" : [
/^\\s*EU-Taxonomy Eligible \\(SFDR\\)\\s*$/i,
/^\\s*EU-taxonomy Eligible % \\(SFDR\\)\\s*$/i
]
}
},
{
"name" : {
"$in" : [
/^\\s*EU-Taxonomy Aligned \\(SFDR\\)\\s*$/i,
/^\\s*EU-taxonomy Aligned % \\(SFDR\\)\\s*$/i
]
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"planNodeId" : 1,
"keyPattern" : {
"companyId" : 1
},
"indexName" : "companyId_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"companyId" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"companyId" : [
"[ObjectId('6222112f21573e00018ef332'), ObjectId('6222112f21573e00018ef332')]",
"[ObjectId('622212e633075400010c9977'), ObjectId('622212e633075400010c9977')]",
"[ObjectId('62811ba5541e3600019730d0'), ObjectId('62811ba5541e3600019730d0')]",
"[ObjectId('65577ce17124dd000199e6d9'), ObjectId('65577ce17124dd000199e6d9')]"
]
}
}
}