[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. |