[SERVER-32555] executionStats.nReturned incorrect for sharded query with limit Created: 05/Jan/18  Updated: 06/Dec/22  Resolved: 25/Apr/18

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

Type: Bug Priority: Major - P3
Reporter: Charlie Swanson Assignee: Backlog - Query Team (Inactive)
Resolution: Duplicate Votes: 0
Labels: todo_in_code
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-29449 Explain of find command does not tran... Backlog
Related
related to SERVER-43466 Complete TODO listed in SERVER-32555 Closed
related to SERVER-44208 Complete TODO listed in SERVER-32555 Closed
Assigned Teams:
Query
Operating System: ALL
Steps To Reproduce:

Executing against a sharded cluster with 2 shards:

mongos> db.adminCommand({enableSharding: "test"})
{
	"ok" : 1,
	"$clusterTime" : {
		"clusterTime" : Timestamp(1515170563, 7),
		"signature" : {
			"hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
			"keyId" : NumberLong(0)
		}
	},
	"operationTime" : Timestamp(1515170563, 7)
}
mongos> db.adminCommand({shardCollection: "test.foo", key: {_id: "hashed"}, initialChunks: 4})
{
	"collectionsharded" : "test.foo",
	"collectionUUID" : UUID("2651991b-cf82-43f6-bd70-8c26d35f2b7f"),
	"ok" : 1,
	"$clusterTime" : {
		"clusterTime" : Timestamp(1515170584, 14),
		"signature" : {
			"hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
			"keyId" : NumberLong(0)
		}
	},
	"operationTime" : Timestamp(1515170584, 6)
}
mongos> db.foo.insert([{_id: 0}, {_id: 1}, {_id: 2}, {_id: 3}, {_id: 4}, {_id: 5}, {_id: 6}, {_id: 7}, {_id: 8}])
BulkWriteResult({
	"writeErrors" : [ ],
	"writeConcernErrors" : [ ],
	"nInserted" : 9,
	"nUpserted" : 0,
	"nMatched" : 0,
	"nModified" : 0,
	"nRemoved" : 0,
	"upserted" : [ ]
})
mongos> sh.status()
--- Sharding Status --- 
  sharding version: {
  	"_id" : 1,
  	"minCompatibleVersion" : 5,
  	"currentVersion" : 6,
  	"clusterId" : ObjectId("5a4faaf8e6190e5786912ddd")
  }
  shards:
        {  "_id" : "shard0000",  "host" : "franklinia:20000",  "state" : 1 }
        {  "_id" : "shard0001",  "host" : "franklinia:20001",  "state" : 1 }
  active mongoses:
        "0.0.0" : 1
  autosplit:
        Currently enabled: no
  balancer:
        Currently enabled:  no
        Currently running:  no
        Failed balancer rounds in last 5 attempts:  0
        Migration Results for the last 24 hours: 
                1 : Success
  databases:
        {  "_id" : "config",  "primary" : "config",  "partitioned" : true }
                config.system.sessions
                        shard key: { "_id" : 1 }
                        unique: false
                        balancing: true
                        chunks:
                                shard0000	1
                        { "_id" : { "$minKey" : 1 } } -->> { "_id" : { "$maxKey" : 1 } } on : shard0000 Timestamp(1, 0) 
        {  "_id" : "test",  "primary" : "shard0001",  "partitioned" : true }
                test.foo
                        shard key: { "_id" : "hashed" }
                        unique: false
                        balancing: true
                        chunks:
                                shard0000	2
                                shard0001	2
                        { "_id" : { "$minKey" : 1 } } -->> { "_id" : NumberLong("-4611686018427387902") } on : shard0000 Timestamp(2, 2) 
                        { "_id" : NumberLong("-4611686018427387902") } -->> { "_id" : NumberLong(0) } on : shard0000 Timestamp(2, 3) 
                        { "_id" : NumberLong(0) } -->> { "_id" : NumberLong("4611686018427387902") } on : shard0001 Timestamp(2, 4) 
                        { "_id" : NumberLong("4611686018427387902") } -->> { "_id" : { "$maxKey" : 1 } } on : shard0001 Timestamp(2, 5) 
 
mongos> db.foo.find({_id: {$gte: 0, $lte: 8}}).limit(3)
{ "_id" : 3 }
{ "_id" : 6 }
{ "_id" : 8 }
mongos> db.foo.explain(true).find({_id: {$gte: 0, $lte: 8}}).limit(3)
{
	"queryPlanner" : {
		"mongosPlannerVersion" : 1,
		"winningPlan" : {
			"stage" : "SHARD_MERGE",
			"shards" : [
				{
					"shardName" : "shard0001",
					"connectionString" : "franklinia:20001",
					"serverInfo" : {
						"host" : "franklinia",
						"port" : 20001,
						"version" : "0.0.0",
						"gitVersion" : "unknown"
					},
					"plannerVersion" : 1,
					"namespace" : "test.foo",
					"indexFilterSet" : false,
					"parsedQuery" : {
						"$and" : [
							{
								"_id" : {
									"$lte" : 8
								}
							},
							{
								"_id" : {
									"$gte" : 0
								}
							}
						]
					},
					"winningPlan" : {
						"stage" : "LIMIT",
						"limitAmount" : 3,
						"inputStage" : {
							"stage" : "FETCH",
							"inputStage" : {
								"stage" : "SHARDING_FILTER",
								"inputStage" : {
									"stage" : "IXSCAN",
									"keyPattern" : {
										"_id" : 1
									},
									"indexName" : "_id_",
									"isMultiKey" : false,
									"multiKeyPaths" : {
										"_id" : [ ]
									},
									"isUnique" : true,
									"isSparse" : false,
									"isPartial" : false,
									"indexVersion" : 2,
									"direction" : "forward",
									"indexBounds" : {
										"_id" : [
											"[0.0, 8.0]"
										]
									}
								}
							}
						}
					},
					"rejectedPlans" : [ ]
				},
				{
					"shardName" : "shard0000",
					"connectionString" : "franklinia:20000",
					"serverInfo" : {
						"host" : "franklinia",
						"port" : 20000,
						"version" : "0.0.0",
						"gitVersion" : "unknown"
					},
					"plannerVersion" : 1,
					"namespace" : "test.foo",
					"indexFilterSet" : false,
					"parsedQuery" : {
						"$and" : [
							{
								"_id" : {
									"$lte" : 8
								}
							},
							{
								"_id" : {
									"$gte" : 0
								}
							}
						]
					},
					"winningPlan" : {
						"stage" : "LIMIT",
						"limitAmount" : 3,
						"inputStage" : {
							"stage" : "FETCH",
							"inputStage" : {
								"stage" : "SHARDING_FILTER",
								"inputStage" : {
									"stage" : "IXSCAN",
									"keyPattern" : {
										"_id" : 1
									},
									"indexName" : "_id_",
									"isMultiKey" : false,
									"multiKeyPaths" : {
										"_id" : [ ]
									},
									"isUnique" : true,
									"isSparse" : false,
									"isPartial" : false,
									"indexVersion" : 2,
									"direction" : "forward",
									"indexBounds" : {
										"_id" : [
											"[0.0, 8.0]"
										]
									}
								}
							}
						}
					},
					"rejectedPlans" : [ ]
				}
			]
		}
	},
	"executionStats" : {
		"nReturned" : 6,
		"executionTimeMillis" : 2,
		"totalKeysExamined" : 6,
		"totalDocsExamined" : 6,
		"executionStages" : {
			"stage" : "SHARD_MERGE",
			"nReturned" : 6,
			"executionTimeMillis" : 2,
			"totalKeysExamined" : 6,
			"totalDocsExamined" : 6,
			"totalChildMillis" : NumberLong(0),
			"shards" : [
				{
					"shardName" : "shard0001",
					"executionSuccess" : true,
					"executionStages" : {
						"stage" : "LIMIT",
						"nReturned" : 3,
						"executionTimeMillisEstimate" : 0,
						"works" : 4,
						"advanced" : 3,
						"needTime" : 0,
						"needYield" : 0,
						"saveState" : 0,
						"restoreState" : 0,
						"isEOF" : 1,
						"invalidates" : 0,
						"limitAmount" : 3,
						"inputStage" : {
							"stage" : "FETCH",
							"nReturned" : 3,
							"executionTimeMillisEstimate" : 0,
							"works" : 3,
							"advanced" : 3,
							"needTime" : 0,
							"needYield" : 0,
							"saveState" : 0,
							"restoreState" : 0,
							"isEOF" : 0,
							"invalidates" : 0,
							"docsExamined" : 3,
							"alreadyHasObj" : 0,
							"inputStage" : {
								"stage" : "SHARDING_FILTER",
								"nReturned" : 3,
								"executionTimeMillisEstimate" : 0,
								"works" : 3,
								"advanced" : 3,
								"needTime" : 0,
								"needYield" : 0,
								"saveState" : 0,
								"restoreState" : 0,
								"isEOF" : 0,
								"invalidates" : 0,
								"chunkSkips" : 0,
								"inputStage" : {
									"stage" : "IXSCAN",
									"nReturned" : 3,
									"executionTimeMillisEstimate" : 0,
									"works" : 3,
									"advanced" : 3,
									"needTime" : 0,
									"needYield" : 0,
									"saveState" : 0,
									"restoreState" : 0,
									"isEOF" : 0,
									"invalidates" : 0,
									"keyPattern" : {
										"_id" : 1
									},
									"indexName" : "_id_",
									"isMultiKey" : false,
									"multiKeyPaths" : {
										"_id" : [ ]
									},
									"isUnique" : true,
									"isSparse" : false,
									"isPartial" : false,
									"indexVersion" : 2,
									"direction" : "forward",
									"indexBounds" : {
										"_id" : [
											"[0.0, 8.0]"
										]
									},
									"keysExamined" : 3,
									"seeks" : 1,
									"dupsTested" : 0,
									"dupsDropped" : 0,
									"seenInvalidated" : 0
								}
							}
						}
					}
				},
				{
					"shardName" : "shard0000",
					"executionSuccess" : true,
					"executionStages" : {
						"stage" : "LIMIT",
						"nReturned" : 3,
						"executionTimeMillisEstimate" : 0,
						"works" : 4,
						"advanced" : 3,
						"needTime" : 0,
						"needYield" : 0,
						"saveState" : 0,
						"restoreState" : 0,
						"isEOF" : 1,
						"invalidates" : 0,
						"limitAmount" : 3,
						"inputStage" : {
							"stage" : "FETCH",
							"nReturned" : 3,
							"executionTimeMillisEstimate" : 0,
							"works" : 3,
							"advanced" : 3,
							"needTime" : 0,
							"needYield" : 0,
							"saveState" : 0,
							"restoreState" : 0,
							"isEOF" : 0,
							"invalidates" : 0,
							"docsExamined" : 3,
							"alreadyHasObj" : 0,
							"inputStage" : {
								"stage" : "SHARDING_FILTER",
								"nReturned" : 3,
								"executionTimeMillisEstimate" : 0,
								"works" : 3,
								"advanced" : 3,
								"needTime" : 0,
								"needYield" : 0,
								"saveState" : 0,
								"restoreState" : 0,
								"isEOF" : 0,
								"invalidates" : 0,
								"chunkSkips" : 0,
								"inputStage" : {
									"stage" : "IXSCAN",
									"nReturned" : 3,
									"executionTimeMillisEstimate" : 0,
									"works" : 3,
									"advanced" : 3,
									"needTime" : 0,
									"needYield" : 0,
									"saveState" : 0,
									"restoreState" : 0,
									"isEOF" : 0,
									"invalidates" : 0,
									"keyPattern" : {
										"_id" : 1
									},
									"indexName" : "_id_",
									"isMultiKey" : false,
									"multiKeyPaths" : {
										"_id" : [ ]
									},
									"isUnique" : true,
									"isSparse" : false,
									"isPartial" : false,
									"indexVersion" : 2,
									"direction" : "forward",
									"indexBounds" : {
										"_id" : [
											"[0.0, 8.0]"
										]
									},
									"keysExamined" : 3,
									"seeks" : 1,
									"dupsTested" : 0,
									"dupsDropped" : 0,
									"seenInvalidated" : 0
								}
							}
						}
					}
				}
			]
		},
		"allPlansExecution" : [
			{
				"shardName" : "shard0001",
				"allPlans" : [ ]
			},
			{
				"shardName" : "shard0000",
				"allPlans" : [ ]
			}
		]
	},
	"ok" : 1,
	"$clusterTime" : {
		"clusterTime" : Timestamp(1515170644, 1),
		"signature" : {
			"hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
			"keyId" : NumberLong(0)
		}
	},
	"operationTime" : Timestamp(1515170644, 1)
}

Participants:

 Description   

For a sharded query with a limit, the number of documents actually returned is no more than the limit given, but the number given in the executionStats.nReturned field is computed as the sum of the number of returned from each shard.

This should be fixed to show the actual number of documents returned.



 Comments   
Comment by Githook User [ 06/Nov/19 ]

Author:

{'name': 'Charlie Swanson', 'username': 'cswanson310', 'email': 'charlie.swanson@mongodb.com'}

Message: SERVER-44208 Update SERVER-32555 TODOs

The ticket was closed as a duplicate of SERVER-29449 which is still
unresolved. So the TODOs should remain but are stale.
Branch: master
https://github.com/mongodb/mongo/commit/a0f929889db6566a92200ac3f1f430f1d042862a

Comment by Charlie Swanson [ 05/Jan/18 ]

It looks like this also impacts the explain output for the distinct command. If each shard returns N distinct values, there may only be N returned (both shards had identical sets), but the reported nReturned will be 2N.

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