[SERVER-27528] partialFilterCondition index ignores $exists:true condition Created: 27/Dec/16  Updated: 03/Jan/17  Resolved: 03/Jan/17

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 3.2.10, 3.4.1
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Kamal Kalra Assignee: Kelsey Schubert
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Participants:

 Description   

I have a collection with a partial index created using the following command:

db.Nodes.createIndex({spaceOwner:1, key:1}, {unique:true, partialFilterCondition: {spaceOwner:{$exists:true}}});

As you can see, I'm trying to set up a unique partial constraint anytime the field "spaceOwner" exists. The combination of spaceOwner / key is always unique anytime the field spaceOwner exists. This is guaranteed by the application. However, I keep getting duplicate key exceptions for when the spaceOwner field doesn't exist which makes sense because in that scenario, there will indeed be duplicates but the index should only check when the spaceOwner key exists, which it doesn't seem to be doing.

Here is an example stack trace:

com.mongodb.MongoWriteException: E11000 duplicate key error collection: NubiDo2.Nodes index: spaceOwner_1_key_1 dup key: { : null, : "tasks" }
	at com.mongodb.MongoCollectionImpl.executeSingleWriteRequest(MongoCollectionImpl.java:487)
	at com.mongodb.MongoCollectionImpl.update(MongoCollectionImpl.java:474)
	at com.mongodb.MongoCollectionImpl.updateOne(MongoCollectionImpl.java:325)
...
...



 Comments   
Comment by Kamal Kalra [ 03/Jan/17 ]

Ah!!! Thank you. Can't believe I missed that.

Comment by James Wahlin [ 03/Jan/17 ]

Hi Kamal,

Partial index filters are supported in MongoDB 3.2. 'partialIndexCondition' was a typo on my part. 'partialFilterCondition' is also not a valid option for index creation. What you should be using is 'partialFilterExpression'. Given that your statement becomes:

db.TestDB.createIndex({spaceOwner:1, key:1}, {unique:true, partialFilterExpression: {spaceOwner:{$exists:true}}})

Thanks,
James

Comment by Kamal Kalra [ 03/Jan/17 ]

My query states "partialFilterCondition", not "partialIndexCondition". I've followed the guidelines in the documentation and I don't see what's wrong with the query expression.

Ex):

db.TestDB.createIndex({spaceOwner:1, key:1}, {unique:true, partialFilterCondition: {spaceOwner:{$exists:true}}})

Comment by James Wahlin [ 03/Jan/17 ]

Hi kamal.kalra@gennubi.com,

Use of 'partialIndexCondition' as an index option has never been supported. Under MongoDB 3.2 you can specify this option but it will be ignored, which we recognize is not ideal behavior and explains the behavior you are seeing.

To help address cases like this, where a wrong keyword is used or a keyword is mis-spelled, we added stricter validation for index creation under SERVER-769. This change is part of our MongoDB 3.4 release and will reject index creation with invalid options.

If you are still encountering issues creating or using this index, I would suggest reading our Partial Indexes documentation which details this feature.

Best,
James

Comment by Kamal Kalra [ 03/Jan/17 ]

This is on MongoDB 3.2.10. I incorrectly listed 3.4.1 in the description (wasn't able to edit it after the issue was submitted). But the syntax seems correct. Mongo (3.2.10) doesn't complain about an invalid index, etc..

Comment by Kelsey Schubert [ 03/Jan/17 ]

Hi kamalkalra,

Thanks for clarifying. I took a second look and it appears that you are specifying the partial index expression incorrectly. Using the command you provided in MongoDB 3.4.1 results in the following error message:

> db.TestCollection.createIndex({spaceOwner:1, key:1}, {unique:true, partialFilterCondition: {spaceOwner:{$exists:true}}});
{
	"ok" : 0,
	"errmsg" : "The field 'partialFilterCondition' is not valid for an index specification. Specification: { key: { spaceOwner: 1.0, key: 1.0 }, name: \"spaceOwner_1_key_1\", unique: true, partialFilterCondition: { spaceOwner: { $exists: true } } }",
	"code" : 197,
	"codeName" : "InvalidIndexSpecificationOption"
}

Please use partialFilterExpression as documented here.

Thank you,
Thomas

Comment by Kamal Kalra [ 03/Jan/17 ]

The field was omitted. The stack trace display null even when the field doesn't exist (which is another issue by itself).

Execute this simple test in the shell and it'll throw an error:

use Test
db.TestCollection.createIndex({spaceOwner:1, key:1}, {unique:true, partialFilterCondition: {spaceOwner:{$exists:true}}});
 
db.TestCollection.insert({key:"hello"});
db.TestCollection.insert({key:"hello"});

WriteResult({
	"nInserted" : 0,
	"writeError" : {
		"code" : 11000,
		"errmsg" : "E11000 duplicate key error collection: Test.TestDB index: spaceOwner_1_key_1 dup key: { : null, : \"hello\" }"
	}
})

Comment by Kelsey Schubert [ 03/Jan/17 ]

Hi kamalkalra,

This behavior is expected when MongoDB is sent a document with the literal null in spaceOwner field. Since the field exists in the document, MongoDB attempts to index the null value. If the field were instead omitted, you should not see duplicate key exceptions.

Kind regards,
Thomas

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