[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

connecting to: odyssey_test
> use foo_test
switched to db foo_test
> db.users.ensureIndex({aliases:1},{sparse:true,unique:true})
> db.users.insert({name:"alice", aliases:[]})
> db.users.insert({name:"bob", aliases:[]})
> db.users.find()
{ "_id" : ObjectId("4e7b5b219521a5c75ce16608"), "name" : "alice", "aliases" : [ ] }
{ "_id" : ObjectId("4e7b5b289521a5c75ce16609"), "name" : "bob", "aliases" : [ ] }

with v2.0.0:

MongoDB shell version: 2.0.0

connecting to: test
> use foo_test
switched to db foo_test
> db.users.ensureIndex({aliases:1},{sparse:true,unique:true})
> db.users.insert({name:"alice", aliases:[]})
> db.users.insert({name:"bob", aliases:[]})
E11000 duplicate key error index: foo_test.users.$aliases_1  dup key: { : undefined }

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:

db.users.ensureIndex({aliases:1},{partialFilterExpression:{aliases:{$type:"string"}},unique:true})
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 3,
	"numIndexesAfter" : 4,
	"ok" : 1
}
> db.users.insert({name:"alice", aliases:[]})
WriteResult({ "nInserted" : 1 })
> db.users.insert({name:"bob", aliases:[]})
WriteResult({ "nInserted" : 1 })
> db.users.insert({name:"asya", aliases:["ak","aysa"]})
WriteResult({ "nInserted" : 1 })
> db.users.insert({name:"joe", aliases:["ak"]})
WriteResult({
	"nInserted" : 0,
	"writeError" : {
		"code" : 11000,
		"errmsg" : "E11000 duplicate key error collection: test.users index: aliases_1 dup key: { : \"ak\" }"
	}
})

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

db.Resource.ensureIndex({suid: 1, "pps.p": 1}, { unique: true, sparse: true, v:0});

and got:

"sprawk/bruyere.sprawk.com:27018,n1-rs1.mongo.sprawk.com:27018,n4-rs1.mongo.sprawk.com:27018" : {
			"createdCollectionAutomatically" : false,
			"numIndexesBefore" : 12,
			"ok" : 0,
			"errmsg" : "E11000 duplicate key error index: jerome5.Resource.$suid_1_pps.p_1  dup key: { : 31340, : null }",
			"code" : 11000
		}

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 SERVER-2258).

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?

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