[SERVER-4815] Creating a unique index should have an ignore null values option Created: 30/Jan/12  Updated: 12/Sep/23

Status: Backlog
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: 2.0.2
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: Alex Sharp Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 6
Labels: indexv3
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query Optimization
Participants:

 Description   

I've seen Issue 2028 (https://jira.mongodb.org/browse/SERVER-2028) and I don't think a sparse index is a good enough solution. One of the reasons I think a sparse index fails as a solution is because when the index is used in a lookup, the documents that do have null values in the indexed field are ignored in search results. Let me give a concrete example of what I think the behavior should be...

Let's say I'm adding a new property to a "users" collection. The new property is an api token, and needs to be unique across the entire collection. I'd like to be able to do db.users.ensureIndex(

{"token": 1, "unique": true}

), but this will of course fail b/c mongo will hit more than one null value. On the other hand, a sparse index here doesn't work because when I try to do a read query on that column where that column would get used (db.users.find({token: {"$ne": null}}).count) the result set excludes all the users b/c it's a new property.



 Comments   
Comment by Asya Kamsky [ 04/May/22 ]

> I'd like to see an option to prevent null values altogether on unique indexes

While not specific to unique indexes, document validation allows creating rules which won't allow explicitly null fields (while allowing the field to not be set if that's appropriate).

Comment by Daniel Sinclair [ 23/Feb/13 ]

I think unique sparse indexes already work the way they should. I'm happy with the differentiation between missing and null fields and I think it's an important distinction.

However, I'd like to see an option to prevent null values altogether on unique indexes. It doesn't make sense to me. Particularly, as the first time you add an entry with a null field it works just fine. I'm picking this up in my unit tests (just about). I'd prefer that if anyone tried to add a document with a null field that's required for the unique index it should reject even the very FIRST entry as an index violation. If I want to use the sparse index, I'll make my fields sparse.

This would trap errors better IMHO.

Comment by Alex Sharp [ 22/May/12 ]

It appears that the sparse option will only work if the documents don't have values set at all for the unique sparse field. If there is a value set, and it is null, document insert will fail due to a uniqueness violation. For unique indexes, null values should behave the same as an unset field.

Comment by Alex Sharp [ 22/May/12 ]

Have there been any updates on this?

Comment by Eliot Horowitz (Inactive) [ 16/Feb/12 ]

This probably should be the default like postgres, et al.

Generated at Thu Feb 08 03:07:04 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.