Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-32555

executionStats.nReturned incorrect for sharded query with limit

    • Query
    • ALL
    • Hide

      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)
      }
      
      Show
      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) }

      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.

            Assignee:
            backlog-server-query Backlog - Query Team (Inactive)
            Reporter:
            charlie.swanson@mongodb.com Charlie Swanson
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: