MongoDB.live, free & fully virtual. June 9th - 10th. Register Now MongoDB.live, free & fully virtual. June 9th - 10th. Register Now

Inconsistent behavior of findAndModify, update when using _id as predicate on sharded collection

I have a collection named messages sharded on [ _id, userId ]

findAndModify with upsert : false will fail if the predicate is only the _id.

For example:

	db.getCollection("messages").findAndModify({
	query: {
	  _id : ObjectId("507f1f77bcf86cd799439011")
	},
	update: 	{
		$set : { "s_messageBody" : "Hello" }
	},
	upsert: false
})

This gives errmsg : “Query for sharded findAndModify must contain the shard key”

Interestingly, the equivalent update query will succeed :

db.getCollection("messages").update(
{
	_id : ObjectId("507f1f77bcf86cd799439011")
},
{
	$set : { "s_messageBody" : "Hello" }
},
{
  upsert: false,
  multi: false   
})

Looking at the explain for the update, I noticed an “IDHACK” plan.

				"winningPlan" : {
					"stage" : "UPDATE",
					"inputStage" : {
						"stage" : "IDHACK"
					}
				},

Can you explain why the query works for a regular update, but not findAndModify?

I am using Mongo 4.2.3