[SERVER-3934] Unique sparse index with array field - can't have two docs with empty arrays (new in v2.0) Created: 23/Sep/11 Updated: 06/Dec/22 Resolved: 10/Apr/18 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Index Maintenance |
| Affects Version/s: | 2.0.0 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Minor - P4 |
| Reporter: | Ben Symonds | Assignee: | Backlog - Query Team (Inactive) |
| Resolution: | Done | Votes: | 4 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Assigned Teams: |
Query
|
| Operating System: | ALL |
| Participants: |
| Description |
|
This seems to be a change in behaviour between 1.8 and 2.0: With v1.8.1: MongoDB shell version: 1.8.1
with v2.0.0: MongoDB shell version: 2.0.0
If I instead create the index with v:0 (to force old-style, pre-v2.0 index type), I don't get the dup key error, i.e. same behaviour as 1.8 (as you would expect). Is this intended? I don't see anything in release notes... |
| Comments |
| Comment by Asya Kamsky [ 25/Jul/18 ] | |||||||||||||||||||||
|
nefiga sorry I missed your comment before. Partial indexes are documented here: https://docs.mongodb.com/manual/core/index-partial/ They only index the documents that satisfy the query in the partialFilterExpression - sparse indexes index every document that has the field present. Empty array is not indexed in the example filter I constructed, that's just part of how partial indexes work. Sparse treats missing field and field that has empty array differently (empty array is a value).
| |||||||||||||||||||||
| Comment by Ben Rotz [ 16/Jul/18 ] | |||||||||||||||||||||
|
@Asya Kamsky Is there any way to find out more information about this technique? I have googled around and cannot find documentation on this feature. I'm not exactly sure what is happening behind the scenes, or if this is even documented behavior? Thanks | |||||||||||||||||||||
| Comment by Asya Kamsky [ 10/Apr/18 ] | |||||||||||||||||||||
|
Closing as partial index alternative makes this possible without using sparse index. | |||||||||||||||||||||
| Comment by Asya Kamsky [ 10/Apr/18 ] | |||||||||||||||||||||
|
Since introduction of partial indexes in 3.2 you can enforce uniqueness of array elements across documents without getting an error for null or empty arrays the following way:
| |||||||||||||||||||||
| Comment by Johnny Shields [ 21/Feb/17 ] | |||||||||||||||||||||
|
This is a major annoyance, can this please be considered for a future version? | |||||||||||||||||||||
| Comment by Nic Cottrell (Personal) [ 23/Jul/14 ] | |||||||||||||||||||||
|
Seems not. I tried
| |||||||||||||||||||||
| Comment by Nic Cottrell (Personal) [ 23/Jul/14 ] | |||||||||||||||||||||
|
Would be interested in this too. Can one use v0 indexes in M2.6+ still? | |||||||||||||||||||||
| Comment by Blake Niemyjski [ 09/Jan/13 ] | |||||||||||||||||||||
|
Hello, Is there any timeline when this is going to be fixed? | |||||||||||||||||||||
| Comment by Ben Symonds [ 31/Oct/11 ] | |||||||||||||||||||||
|
Hi Aaron, Ok, thanks for the update. Yeah, we'll just use a v0 index for now. Also thanks for the heads up on the v0 bugs. I think we'll be ok as we only ever query that field looking for a value, e.g. db.users.find( {aliases:"superman"}). | |||||||||||||||||||||
| Comment by Aaron Staple [ 31/Oct/11 ] | |||||||||||||||||||||
|
Hi Ben, We're discussing what to do about this right now. For the moment, as a workaround you can can create a v0 index to get the behavior you are used to from 1.8 db.users.ensureIndex( {aliases:1}, {sparse:true,unique:true,v:0}) But bear in mind that there are some issues around matching empty arrays in v0 indexes (for example | |||||||||||||||||||||
| Comment by Ben Symonds [ 28/Oct/11 ] | |||||||||||||||||||||
|
Hi Kyle. Any update on this? It's a small thing, but bit of a faff to work around it in our app, so wondering if it'll get fixed any time soon? |