-
Type:
Bug
-
Resolution: Works as Designed
-
Priority:
Minor - P4
-
None
-
Affects Version/s: 5.0.13
-
Component/s: None
-
None
-
ALL
-
(copied to CRM)
-
None
-
None
-
None
-
None
-
None
-
None
-
None
The customer uses a wildcard index on a collection with nested documents/sub-document fields.
For a sample document, please take a look below.
db.collTest.insertOne({
"identifier" : {
"prids" :
{ "MoveMoneyId" : null }
}})
Wildcard Index definition:
{ 'identifier.prids.$**': 1 }
As per wildcard index documentation on unsupported queries and aggregation, the inverse (not null ) is definitely not supported, but null should be.
So when we check the below query explain() stats, we see COLLSCAN instead of IXSCAN.
db.collTest.find({'identifier.prids.MoveMoneyId': null }).explain("executionStats")
The query undergoes the IXSCAN stage for any other value except when using null.
As a workaround, I could mitigate this behaviour by using the BSON Type check for null, and it used the wildcard index perfectly.
db.collTest.find({ "identifier.prids.MoveMoneyId": {$type:10}}).explain("executionStats")
I am attaching explain() for both if it needs to be reviewed.