[SERVER-3685] Unique index with sparse does not work Created: 25/Aug/11  Updated: 03/Nov/14  Resolved: 25/Aug/11

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

Type: Bug Priority: Major - P3
Reporter: Tanin Na Nakorn Assignee: Unassigned
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Windows 7


Operating System: ALL
Participants:

 Description   

In my mongo shell, I do this:

db.members.ensureIndex(

{facebook_id:1}

,

{unique:true,sparse:true}

)

  • I also try db.members.ensureIndex( {facebook_id:1}

    ,

    {sparse:true,unique:true}

    )

Then I try to insert 2 records with facebook_id = null

I have got this message:

E11000 duplicate key error index: writepub.members.$facebook_id_1 dup key: { :null }



 Comments   
Comment by kic [ 03/Nov/14 ]

That is what is sparse for:

Sparse indexes only contain entries for documents that have the indexed field, even if the index field contains a null value. The index skips over any document that is missing the indexed field.

http://docs.mongodb.org/manual/core/index-sparse/#index-type-sparse

Comment by Eliot Horowitz (Inactive) [ 25/Aug/11 ]

null counts as being a field.
The field has to be missing for it not to be indexed

Comment by Scott Hernandez (Inactive) [ 25/Aug/11 ]

(tested on 1.9.2)
Look suspect when using null:

> db.users.dropIndexes()
> db.users.find()

{ "_id" : 1, "facebook_id" : null }

> db.users.ensureIndex(

{facebook_id:true}

,

{sparse:true, unique:true}

)
> db.users.$facebook_id_.count()
1
> db.users.insert({_id:2, facebook_id:null})
E11000 duplicate key error index: test.users.$facebook_id_ dup key: { : null }
> db.users.find(

{facebook_id : null}

).explain()
{
"cursor" : "BtreeCursor facebook_id_",
"nscanned" : 1,
"nscannedObjects" : 1,
"n" : 1,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" :

{ "facebook_id" : [ [ null, null ] ] }

}
PRIMARY> db.users.getIndexes()
[
{
"v" : 1,
"key" :

{ "_id" : 1 }

,
"ns" : "test.users",
"name" : "id"
},
{
"v" : 1,
"key" :

{ "facebook_id" : true }

,
"unique" : true,
"ns" : "test.users",
"name" : "facebook_id_",
"sparse" : true
}
]

----------------------

> db.users.remove()
> db.users.insert({_id:1})
> db.users.insert({_id:2})
> db.users.insert({_id:3})

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