Details
-
Bug
-
Resolution: Done
-
Major - P3
-
None
-
7.0.2
-
None
-
None
-
ALL
-
Description
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')]" |
]
|
}
|
}
|
}
|