[SERVER-75544] Wildcard Index not working as expected for nested field value null Created: 31/Mar/23  Updated: 27/Oct/23  Resolved: 06/Apr/23

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: 5.0.13
Fix Version/s: None

Type: Bug Priority: Minor - P4
Reporter: Janpreet Singh Assignee: Alexander Ignatyev
Resolution: Works as Designed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File explain_normalCOLLSCAN.js     File explain_using_BSONTypeCheck.js    
Issue Links:
Related
is related to DOCS-16016 [Server] Incorrect documentation sect... Closed
Operating System: ALL
Participants:
Case:

 Description   

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.



 Comments   
Comment by Alexander Ignatyev [ 06/Apr/23 ]

Hi janpreet.singh@mongodb.com, thank you for reporting this.

In fact, wildcard indexes, like other sparse indexes, cannot answer queries for incomplete results, such as {$eq: null} (another example is {$exists: true}). This is because the semantics of {$eq: null} is such that it must match all documents where the field is explicitly null or missing, and wildcard indexes, being sparse, do not index the latter documents.

Your workaround with {$type: 10} works fine since $type does not have the "missing field" semantics I mentioned above and can be answered by sparse indexes.

It seems to me that our documentation is misleading here and I filled in the ticket DOCS-16016 to address the issue.

Therefore, I am closing this ticket with the resolution "Works as designed", please reopen the ticket if you have more questions or reach out to me.

Generated at Thu Feb 08 06:30:27 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.