[SERVER-49845] allow indices to optionally cover missing values Created: 23/Jul/20  Updated: 28/Jul/20  Resolved: 28/Jul/20

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

Type: New Feature Priority: Minor - P4
Reporter: Jeremy Todd Assignee: Asya Kamsky
Resolution: Duplicate Votes: 0
Labels: qopt-team
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-18861 Queries matching null value should be... Backlog
Participants:

 Description   

I often need to run queries of the form {a: {$ne:null}}, i.e. find documents in which the field "a" is not missing and not equal to null. As I understand it, an index on "a" will not cover this query because the index will not include documents where "a" is missing.

In these cases I find myself forced to always include a value for "a". This isn't hard to do - generally there's always some value I can reserve for this purpose (often "null" of course, but sometimes another value if "null" doesn't mean the same thing as a missing value).

It seems to me that mongo could do this for me transparently (by reserving a value to indicate "missing" and including those documents in the index). I completely understand that minimizing index size is important, and this wouldn't be appropriate in many contexts, and certainly not as a default. Nevertheless, this comes up over and over again (I've been running an expansive production environment on mongo for many years). So I think it would make an incredibly useful option when creating an index.

Apologies if I'm missing something about why this isn't feasible (or perhaps feasible but not simple) to implement, or why it's not a good idea. I tried to cover this in a bit more detail on stack overflow before submitting this request:

https://stackoverflow.com/questions/63003917

 



 Comments   
Comment by Asya Kamsky [ 28/Jul/20 ]

I'm closing this as fundamentally being the same as SERVER-18861 - there are some cases where null equality still causes a FETCH and a less efficient plan to be selected and you may have been looking at one of them.

Comment by Jeremy Todd [ 28/Jul/20 ]

Understood - sorry for the bogus feature request! I must have been thinking about queries of this form not leading to COUNT_SCAN operations, discussed here:

https://mongodb-user.narkive.com/MKpJkCNh/count-scan-vs-ixscan-when-query-includes-null

which led to feature request SERVER-18861.

Those are all issues separate from this feature request though.

 

Comment by Asya Kamsky [ 28/Jul/20 ]

SERVER-27646 resolved this for 4.2 - if you are using older server you may see different behavior (related ticket SERVER-36465 was also fixed for 4.2)

Comment by Asya Kamsky [ 28/Jul/20 ]

> I often need to run queries of the form {a: {$ne:null}}, i.e. find documents in which the field "a" is not missing and not equal to null. As I understand it, an index on "a" will not cover this query because the index will not include documents where "a" is missing.

This understanding is incorrect - an index will cover this.

Comment by Jonathan Streets (Inactive) [ 24/Jul/20 ]

hi Jeremy,
Thank you for the example and the link to the discussion on stackoverflow. We're assigning this ticket to the appropriate team to be evaluated against our currently planned work. Updates will be posted on this ticket as they happen.
Jon

Generated at Thu Feb 08 05:21:00 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.