[SERVER-24206] can not get data when using db.coll.find({a:null}) Created: 19/May/16  Updated: 16/Jun/16  Resolved: 16/Jun/16

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

Type: Bug Priority: Major - P3
Reporter: liyang zhou Assignee: Kelsey Schubert
Resolution: Cannot Reproduce Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Steps To Reproduce:

rs0:SECONDARY> db.members.count({ isQuited: false, _teamId: null, _boundToObjectId: ObjectId('51762b8f78cfa9f357000011') })
0   
 
rs0:SECONDARY> db.members.find({ isQuited: false, _teamId: null, _boundToObjectId: ObjectId('51762b8f78cfa9f357000011') })
rs0:SECONDARY> 
 
 
rs0:SECONDARY> db.members.count({ isQuited: false, _teamId:{$exists:false}, _boundToObjectId: ObjectId('51762b8f78cfa9f357000011') })
76  

rs0:SECONDARY> db.members.find({ isQuited: false, _teamId: null, _boundToObjectId: ObjectId('51762b8f78cfa9f357000011') }).explain("executionStats")
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "XXX.members",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [
                                {
                                        "_boundToObjectId" : {
                                                "$eq" : ObjectId("51762b8f78cfa9f357000011")
                                        }
                                },
                                {
                                        "_teamId" : {
                                                "$eq" : null
                                        }
                                },
                                {
                                        "isQuited" : {
                                                "$eq" : false
                                        }
                                }
                        ]
                },
                "winningPlan" : {
                        "stage" : "FETCH",
                        "filter" : {
                                "$and" : [
                                        {
                                                "_teamId" : {
                                                        "$eq" : null
                                                }
                                        },
                                        {
                                                "isQuited" : {
                                                        "$eq" : false
                                                }
                                        }
                                ]
                        },
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "_boundToObjectId" : 1
                                },
                                "indexName" : "_boundToObjectId_1",
                                "isMultiKey" : false,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "_boundToObjectId" : [
                                                "[ObjectId('51762b8f78cfa9f357000011'), ObjectId('51762b8f78cfa9f357000011')]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 76,
                "executionTimeMillis" : 8,
                "totalKeysExamined" : 92,
                "totalDocsExamined" : 92,
                "executionStages" : {
                        "stage" : "FETCH",
                        "filter" : {
                                "$and" : [
                                        {
                                                "_teamId" : {
                                                        "$eq" : null
                                                }
                                        },
                                        {
                                                "isQuited" : {
                                                        "$eq" : false
                                                }
                                        }
                                ]
                        },
                        "nReturned" : 76,
                        "executionTimeMillisEstimate" : 0,
                        "works" : 93,
                        "advanced" : 76,
                        "needTime" : 16,
                        "needFetch" : 0,
                        "saveState" : 0,
                        "restoreState" : 0,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "docsExamined" : 92,
                        "alreadyHasObj" : 0,
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "nReturned" : 92,
                                "executionTimeMillisEstimate" : 0,
                                "works" : 93,
                                "advanced" : 92,
                                "needTime" : 0,
                                "needFetch" : 0,
                                "saveState" : 0,
                                "restoreState" : 0,
                                "isEOF" : 1,
                                "invalidates" : 0,
                                "keyPattern" : {
                                        "_boundToObjectId" : 1
                                },
                                "indexName" : "_boundToObjectId_1",
                                "isMultiKey" : false,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "_boundToObjectId" : [
                                                "[ObjectId('51762b8f78cfa9f357000011'), ObjectId('51762b8f78cfa9f357000011')]"
                                        ]
                                },
                                "keysExamined" : 92,
                                "dupsTested" : 0,
                                "dupsDropped" : 0,
                                "seenInvalidated" : 0,
                                "matchTested" : 0
                        }
                }
        },
        "serverInfo" : {
                "host" : "xxxxxxxxxx",
                "port" : 27017,
                "version" : "3.0.7",
                "gitVersion" : "6ce7cbe8c6b899552dadd907604559806aa2e9bd"
        },
        "ok" : 1
}                                                          

rs0:SECONDARY> db.members.getPlanCache().getPlansByQuery({isQuited: false, _teamId: null, _boundToObjectId: ObjectId('51762b8f78cfa9f357000011') })
[
        {
                "details" : {
                        "solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf { _teamId: 1.0 }, pos: 0\n---Leaf \n)"
                },
                "reason" : {
                        "score" : 1.5003000000000002,
                        "stats" : {
                                "stage" : "LIMIT",
                                "nReturned" : 1,
                                "executionTimeMillisEstimate" : 0,
                                "works" : 2,
                                "advanced" : 1,
                                "needTime" : 1,
                                "needFetch" : 0,
                                "saveState" : 0,
                                "restoreState" : 0,
                                "isEOF" : 1,
                                "invalidates" : 0,
                                "limitAmount" : 0,
                                "inputStage" : {
                                        "stage" : "FETCH",
                                        "filter" : {
                                                "$and" : [
                                                        {
                                                                "_boundToObjectId" : {
                                                                        "$eq" : ObjectId("572866b38b35ba316d9d41af")
                                                                }
                                                        },
                                                        {
                                                                "isQuited" : {
                                                                        "$eq" : false
                                                                }
                                                        }
                                                ]
                                        },
                                        "nReturned" : 1,
                                        "executionTimeMillisEstimate" : 0,
                                        "works" : 2,
                                        "advanced" : 1,
                                        "needTime" : 1,
                                        "needFetch" : 0,
                                        "saveState" : 0,
                                        "restoreState" : 0,
                                        "isEOF" : 0,
                                        "invalidates" : 0,
                                        "docsExamined" : 2,
                                        "alreadyHasObj" : 0,
                                        "inputStage" : {
                                                "stage" : "IXSCAN",
                                                "nReturned" : 2,
                                                "executionTimeMillisEstimate" : 0,
                                                "works" : 2,
                                                "advanced" : 2,
                                                "needTime" : 0,
                                                "needFetch" : 0,
                                                "saveState" : 0,
                                                "restoreState" : 0,
                                                "isEOF" : 0,
                                                "invalidates" : 0,
                                                "keyPattern" : {
                                                        "_teamId" : 1
                                                },
                                                "indexName" : "_teamId_1",
                                                "isMultiKey" : false,
                                                "direction" : "forward",
                                                "indexBounds" : {
                                                        "_teamId" : [
                                                                "[ObjectId('56fb4e0fc6ec2b612e7e6214'), ObjectId('56fb4e0fc6ec2b612e7e6214')]"
                                                        ]
                                                },
                                                "keysExamined" : 2,
                                                "dupsTested" : 0,
                                                "dupsDropped" : 0,
                                                "seenInvalidated" : 0,
                                                "matchTested" : 0
                                        }
                                }
                        }
                },
                "feedback" : {
                        "nfeedback" : 20,
                        "averageScore" : NaN,
                        "stdDevScore" : NaN,
                        "scores" : [
                                {
                                        "score" : NaN
                                },
                                {
                                        "score" : NaN
                                },
                                {
                                        "score" : NaN
                                },
                                {
                                        "score" : NaN
                                },
                                {
                                        "score" : NaN
                                },
                                {
                                        "score" : NaN
                                },
                                {
                                        "score" : NaN
                                },
                                {
                                        "score" : NaN
                                },
                                {
                                        "score" : NaN
                                },
                                {
                                        "score" : NaN
                                },
                                {
                                        "score" : NaN
                                },
                                {
                                        "score" : NaN
                                },
                                {
                                        "score" : NaN
                                },
                                {
                                        "score" : NaN
                                },
                                {
                                        "score" : NaN
                                },
                                {
                                        "score" : NaN
                                },
                                {
                                        "score" : NaN
                                },
                                {
                                        "score" : NaN
                                },
                                {
                                        "score" : NaN
                                },
                                {
                                        "score" : NaN
                                }
                        ]
                },
                "filterSet" : false
        },
        {
                "details" : {
                        "solution" : "(index-tagged expression tree: tree=Node\n---Leaf { _boundToObjectId: 1.0 }, pos: 0\n---Leaf \n---Leaf \n)"
                },
                "reason" : {
                        "score" : 1.0003,
                        "stats" : {
                                "stage" : "LIMIT",
                                "nReturned" : 0,
                                "executionTimeMillisEstimate" : 0,
                                "works" : 2,
                                "advanced" : 0,
                                "needTime" : 2,
                                "needFetch" : 0,
                                "saveState" : 0,
                                "restoreState" : 0,
                                "isEOF" : 0,
                                "invalidates" : 0,
                                "limitAmount" : 1,
                                "inputStage" : {
                                        "stage" : "FETCH",
                                        "filter" : {
                                                "$and" : [
                                                        {
                                                                "_teamId" : {
                                                                        "$eq" : ObjectId("56fb4e0fc6ec2b612e7e6214")
                                                                }
                                                        },
                                                        {
                                                                "isQuited" : {
                                                                        "$eq" : false
                                                                }
                                                        }
                                                ]
                                        },
                                        "nReturned" : 0,
                                        "executionTimeMillisEstimate" : 0,
                                        "works" : 2,
                                        "advanced" : 0,
                                        "needTime" : 2,
                                        "needFetch" : 0,
                                        "saveState" : 0,
                                        "restoreState" : 0,
                                        "isEOF" : 0,
                                        "invalidates" : 0,
                                        "docsExamined" : 2,
                                        "alreadyHasObj" : 0,
                                        "inputStage" : {
                                                "stage" : "IXSCAN",
                                                "nReturned" : 2,
                                                "executionTimeMillisEstimate" : 0,
                                                "works" : 2,
                                                "advanced" : 2,
                                                "needTime" : 0,
                                                "needFetch" : 0,
                                                "saveState" : 0,
                                                "restoreState" : 0,
                                                "isEOF" : 0,
                                                "invalidates" : 0,
                                                "keyPattern" : {
                                                        "_boundToObjectId" : 1
                                                },
                                                "indexName" : "_boundToObjectId_1",
                                                "isMultiKey" : false,
                                                "direction" : "forward",
                                                "indexBounds" : {
                                                        "_boundToObjectId" : [
                                                                "[ObjectId('572866b38b35ba316d9d41af'), ObjectId('572866b38b35ba316d9d41af')]"
                                                        ]
                                                },
                                                "keysExamined" : 2,
                                                "dupsTested" : 0,
                                                "dupsDropped" : 0,
                                                "seenInvalidated" : 0,
                                                "matchTested" : 0
                                        }
                                }
                        }
                },
                "feedback" : {
 
                },
                "filterSet" : false
        },
        {
                "details" : {
                        "solution" : "(index-tagged expression tree: tree=Node\n---Leaf { _boundToObjectId: 1.0 }, pos: 0\n---Leaf { _teamId: 1.0 }, pos: 0\n---Leaf \n)"
                },
                "reason" : {
                        "score" : 1.0002,
                        "stats" : {
                                "stage" : "LIMIT",
                                "nReturned" : 0,
                                "executionTimeMillisEstimate" : 0,
                                "works" : 2,
                                "advanced" : 0,
                                "needTime" : 2,
                                "needFetch" : 0,
                                "saveState" : 0,
                                "restoreState" : 0,
                                "isEOF" : 0,
                                "invalidates" : 0,
                                "limitAmount" : 1,
                                "inputStage" : {
                                        "stage" : "FETCH",
                                        "filter" : {
                                                "$and" : [
                                                        {
                                                                "_boundToObjectId" : {
                                                                        "$eq" : ObjectId("572866b38b35ba316d9d41af")
                                                                }
                                                        },
                                                        {
                                                                "_teamId" : {
                                                                        "$eq" : ObjectId("56fb4e0fc6ec2b612e7e6214")
                                                                }
                                                        },
                                                        {
                                                                "isQuited" : {
                                                                        "$eq" : false
                                                                }
                                                        }
                                                ]
                                        },
                                        "nReturned" : 0,
                                        "executionTimeMillisEstimate" : 0,
                                        "works" : 2,
                                        "advanced" : 0,
                                        "needTime" : 2,
                                        "needFetch" : 0,
                                        "saveState" : 0,
                                        "restoreState" : 0,
                                        "isEOF" : 0,
                                        "invalidates" : 0,
                                        "docsExamined" : 0,
                                        "alreadyHasObj" : 0,
                                        "inputStage" : {
                                                "stage" : "AND_SORTED",
                                                "nReturned" : 0,
                                                "executionTimeMillisEstimate" : 0,
                                                "works" : 2,
                                                "advanced" : 0,
                                                "needTime" : 2,
                                                "needFetch" : 0,
                                                "saveState" : 0,
                                                "restoreState" : 0,
                                                "isEOF" : 0,
                                                "invalidates" : 0,
                                                "flagged" : 0,
                                                "matchTested" : 0,
                                                "failedAnd_0" : 0,
                                                "failedAnd_1" : 0,
                                                "inputStages" : [
                                                        {
                                                                "stage" : "IXSCAN",
                                                                "nReturned" : 1,
                                                                "executionTimeMillisEstimate" : 0,
                                                                "works" : 1,
                                                                "advanced" : 1,
                                                                "needTime" : 0,
                                                                "needFetch" : 0,
                                                                "saveState" : 0,
                                                                "restoreState" : 0,
                                                                "isEOF" : 0,
                                                                "invalidates" : 0,
                                                                "keyPattern" : {
                                                                        "_boundToObjectId" : 1
                                                                },
                                                                "indexName" : "_boundToObjectId_1",
                                                                "isMultiKey" : false,
                                                                "direction" : "forward",
                                                                "indexBounds" : {
                                                                        "_boundToObjectId" : [
                                                                                "[ObjectId('572866b38b35ba316d9d41af'), ObjectId('572866b38b35ba316d9d41af')]"
                                                                        ]
                                                                },
                                                                "keysExamined" : 1,
                                                                "dupsTested" : 0,
                                                                "dupsDropped" : 0,
                                                                "seenInvalidated" : 0,
                                                                "matchTested" : 0
                                                        },
                                                        {
                                                                "stage" : "IXSCAN",
                                                                "nReturned" : 1,
                                                                "executionTimeMillisEstimate" : 0,
                                                                "works" : 1,
                                                                "advanced" : 1,
                                                                "needTime" : 0,
                                                                "needFetch" : 0,
                                                                "saveState" : 0,
                                                                "restoreState" : 0,
                                                                "isEOF" : 0,
                                                                "invalidates" : 0,
                                                                "keyPattern" : {
                                                                        "_teamId" : 1
                                                                },
                                                                "indexName" : "_teamId_1",
                                                                "isMultiKey" : false,
                                                                "direction" : "forward",
                                                                "indexBounds" : {
                                                                        "_teamId" : [
                                                                                "[ObjectId('56fb4e0fc6ec2b612e7e6214'), ObjectId('56fb4e0fc6ec2b612e7e6214')]"
                                                                        ]
                                                                },
                                                                "keysExamined" : 1,
                                                                "dupsTested" : 0,
                                                                "dupsDropped" : 0,
                                                                "seenInvalidated" : 0,
                                                                "matchTested" : 0
                                                        }
                                                ]
                                        }
                                }
                        }
                },
                "feedback" : {
 
                },
                "filterSet" : false
        }
]
rs0:SECONDARY>                                                            

rs0:SECONDARY> db.members.getPlanCache().clear()
rs0:SECONDARY> db.members.count({ isQuited: false, _teamId: null, _boundToObjectId: ObjectId('51762b8f78cfa9f357000011') })
76    

{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "XXX.members",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [
                                {
                                        "_boundToObjectId" : {
                                                "$eq" : ObjectId("51762b8f78cfa9f357000011")
                                        }
                                },
                                {
                                        "_teamId" : {
                                                "$eq" : null
                                        }
                                },
                                {
                                        "isQuited" : {
                                                "$eq" : false
                                        }
                                }
                        ]
                },
                "winningPlan" : {
                        "stage" : "FETCH",
                        "filter" : {
                                "$and" : [
                                        {
                                                "_teamId" : {
                                                        "$eq" : null
                                                }
                                        },
                                        {
                                                "isQuited" : {
                                                        "$eq" : false
                                                }
                                        }
                                ]
                        },
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "_boundToObjectId" : 1
                                },
                                "indexName" : "_boundToObjectId_1",
                                "isMultiKey" : false,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "_boundToObjectId" : [
                                                "[ObjectId('51762b8f78cfa9f357000011'), ObjectId('51762b8f78cfa9f357000011')]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 76,
                "executionTimeMillis" : 8,
                "totalKeysExamined" : 92,
                "totalDocsExamined" : 92,
                "executionStages" : {
                        "stage" : "FETCH",
                        "filter" : {
                                "$and" : [
                                        {
                                                "_teamId" : {
                                                        "$eq" : null
                                                }
                                        },
                                        {
                                                "isQuited" : {
                                                        "$eq" : false
                                                }
                                        }
                                ]
                        },
                        "nReturned" : 76,
                        "executionTimeMillisEstimate" : 0,
                        "works" : 93,
                        "advanced" : 76,
                        "needTime" : 16,
                        "needFetch" : 0,
                        "saveState" : 0,
                        "restoreState" : 0,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "docsExamined" : 92,
                        "alreadyHasObj" : 0,
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "nReturned" : 92,
                                "executionTimeMillisEstimate" : 0,
                                "works" : 93,
                                "advanced" : 92,
                                "needTime" : 0,
                                "needFetch" : 0,
                                "saveState" : 0,
                                "restoreState" : 0,
                                "isEOF" : 1,
                                "invalidates" : 0,
                                "keyPattern" : {
                                        "_boundToObjectId" : 1
                                },
                                "indexName" : "_boundToObjectId_1",
                                "isMultiKey" : false,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "_boundToObjectId" : [
                                                "[ObjectId('51762b8f78cfa9f357000011'), ObjectId('51762b8f78cfa9f357000011')]"
                                        ]
                                },
                                "keysExamined" : 92,
                                "dupsTested" : 0,
                                "dupsDropped" : 0,
                                "seenInvalidated" : 0,
                                "matchTested" : 0
                        }
                }
        },
        "serverInfo" : {
                "host" : "xxxxx",
                "port" : 27017,
                "version" : "3.0.7",
                "gitVersion" : "6ce7cbe8c6b899552dadd907604559806aa2e9bd"
        },
        "ok" : 1
}                                                        

Participants:

 Description   

Hey guys
we met a problem when using the query like this

db.members.count({ isQuited: false, _teamId: null, _boundToObjectId: ObjectId('51762b8f78cfa9f357000011') })

We are using a replica set.
when we run this query on primary ,it`s ok and returns 76.
But when we run this query on the secondary ,it returns 0.but when we run a query with null on the other collection ,it`s ok.
At the same time we can use

db.members.count({ isQuited: false, _teamId:{$exists:false}, _boundToObjectId: ObjectId('51762b8f78cfa9f357000011') })

this query and return 76.

But after db.members.getPlanCache().clear() . the query with null is ok!

but the problem will appear again.

But the explain return before getPlanCache().clear() is the same with after.



 Comments   
Comment by Kelsey Schubert [ 13/Jun/16 ]

Hi 378013446@qq.com,

Since my attempt to reproduce this behavior was unsuccessful, we still need some logs to determine what is going on here. Would you please provide the following information?

  1. the logs for the query that does not return any results at verbosity level 5
  2. the logs for the query that returns 76 results at verbosity level 5

Thank you,
Thomas

Comment by Kelsey Schubert [ 27/May/16 ]

Hi 378013446@qq.com,

Thank you for additional information. Unfortunately, I was unable to reproduce this issue on my side. I inserted the sample documents you provided as well as one additional document that I used to to ensure that the plan cache used the _teamId index rather than _boundToObjectId before executing the count command. Please see my output below:

> db.foo.find({isQuited: false, _teamId:true, _boundToObjectId: ObjectId("51762b8f78cfa9f357000011")})
{ "_id" : ObjectId("57488b4b5f6728f91f784060"), "isQuited" : false, "_teamId" : true, "_boundToObjectId" : ObjectId("51762b8f78cfa9f357000011") }
> db.foo.getPlanCache().getPlansByQuery({ isQuited: false, _teamId: null, _boundToObjectId: ObjectId("51762b8f78cfa9f357000011") })
[
	{
		"details" : {
			"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf { _teamId: 1.0 }, pos: 0\n---Leaf \n)"
		},
		"reason" : {
			"score" : 1.5003000000000002,
			"stats" : {
				"stage" : "KEEP_MUTATIONS",
				"nReturned" : 1,
				"executionTimeMillisEstimate" : 0,
				"works" : 2,
				"advanced" : 1,
				"needTime" : 0,
				"needFetch" : 0,
				"saveState" : 0,
				"restoreState" : 0,
				"isEOF" : 1,
				"invalidates" : 0,
				"inputStage" : {
					"stage" : "FETCH",
					"filter" : {
						"$and" : [
							{
								"_boundToObjectId" : {
									"$eq" : ObjectId("51762b8f78cfa9f357000011")
								}
							},
							{
								"isQuited" : {
									"$eq" : false
								}
							}
						]
					},
					"nReturned" : 1,
					"executionTimeMillisEstimate" : 0,
					"works" : 2,
					"advanced" : 1,
					"needTime" : 0,
					"needFetch" : 0,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 1,
					"invalidates" : 0,
					"docsExamined" : 1,
					"alreadyHasObj" : 0,
					"inputStage" : {
						"stage" : "IXSCAN",
						"nReturned" : 1,
						"executionTimeMillisEstimate" : 0,
						"works" : 1,
						"advanced" : 1,
						"needTime" : 0,
						"needFetch" : 0,
						"saveState" : 0,
						"restoreState" : 0,
						"isEOF" : 1,
						"invalidates" : 0,
						"keyPattern" : {
							"_teamId" : 1
						},
						"indexName" : "_teamId_1",
						"isMultiKey" : false,
						"direction" : "forward",
						"indexBounds" : {
							"_teamId" : [
								"[true, true]"
							]
						},
						"keysExamined" : 1,
						"dupsTested" : 0,
						"dupsDropped" : 0,
						"seenInvalidated" : 0,
						"matchTested" : 0
					}
				}
			}
		},
		"feedback" : {
			"nfeedback" : 0,
			"averageScore" : 0,
			"stdDevScore" : 0,
			"scores" : [ ]
		},
		"filterSet" : false
	},
	{
		"details" : {
			"solution" : "(index-tagged expression tree: tree=Node\n---Leaf { _boundToObjectId: 1.0 }, pos: 0\n---Leaf \n---Leaf \n)"
		},
		"reason" : {
			"score" : 1.0003,
			"stats" : {
				"stage" : "KEEP_MUTATIONS",
				"nReturned" : 0,
				"executionTimeMillisEstimate" : 0,
				"works" : 2,
				"advanced" : 0,
				"needTime" : 2,
				"needFetch" : 0,
				"saveState" : 0,
				"restoreState" : 0,
				"isEOF" : 0,
				"invalidates" : 0,
				"inputStage" : {
					"stage" : "FETCH",
					"filter" : {
						"$and" : [
							{
								"_teamId" : {
									"$eq" : true
								}
							},
							{
								"isQuited" : {
									"$eq" : false
								}
							}
						]
					},
					"nReturned" : 0,
					"executionTimeMillisEstimate" : 0,
					"works" : 2,
					"advanced" : 0,
					"needTime" : 2,
					"needFetch" : 0,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 0,
					"invalidates" : 0,
					"docsExamined" : 2,
					"alreadyHasObj" : 0,
					"inputStage" : {
						"stage" : "IXSCAN",
						"nReturned" : 2,
						"executionTimeMillisEstimate" : 0,
						"works" : 2,
						"advanced" : 2,
						"needTime" : 0,
						"needFetch" : 0,
						"saveState" : 0,
						"restoreState" : 0,
						"isEOF" : 0,
						"invalidates" : 0,
						"keyPattern" : {
							"_boundToObjectId" : 1
						},
						"indexName" : "_boundToObjectId_1",
						"isMultiKey" : false,
						"direction" : "forward",
						"indexBounds" : {
							"_boundToObjectId" : [
								"[ObjectId('51762b8f78cfa9f357000011'), ObjectId('51762b8f78cfa9f357000011')]"
							]
						},
						"keysExamined" : 2,
						"dupsTested" : 0,
						"dupsDropped" : 0,
						"seenInvalidated" : 0,
						"matchTested" : 0
					}
				}
			}
		},
		"feedback" : {
			
		},
		"filterSet" : false
	},
	{
		"details" : {
			"solution" : "(index-tagged expression tree: tree=Node\n---Leaf { _boundToObjectId: 1.0 }, pos: 0\n---Leaf { _teamId: 1.0 }, pos: 0\n---Leaf \n)"
		},
		"reason" : {
			"score" : 1.0002,
			"stats" : {
				"stage" : "KEEP_MUTATIONS",
				"nReturned" : 0,
				"executionTimeMillisEstimate" : 0,
				"works" : 2,
				"advanced" : 0,
				"needTime" : 2,
				"needFetch" : 0,
				"saveState" : 0,
				"restoreState" : 0,
				"isEOF" : 0,
				"invalidates" : 0,
				"inputStage" : {
					"stage" : "FETCH",
					"filter" : {
						"isQuited" : {
							"$eq" : false
						}
					},
					"nReturned" : 0,
					"executionTimeMillisEstimate" : 0,
					"works" : 2,
					"advanced" : 0,
					"needTime" : 2,
					"needFetch" : 0,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 0,
					"invalidates" : 0,
					"docsExamined" : 0,
					"alreadyHasObj" : 0,
					"inputStage" : {
						"stage" : "AND_SORTED",
						"nReturned" : 0,
						"executionTimeMillisEstimate" : 0,
						"works" : 2,
						"advanced" : 0,
						"needTime" : 2,
						"needFetch" : 0,
						"saveState" : 0,
						"restoreState" : 0,
						"isEOF" : 0,
						"invalidates" : 0,
						"flagged" : 0,
						"matchTested" : 0,
						"failedAnd_0" : 1,
						"failedAnd_1" : 0,
						"inputStages" : [
							{
								"stage" : "IXSCAN",
								"nReturned" : 1,
								"executionTimeMillisEstimate" : 0,
								"works" : 1,
								"advanced" : 1,
								"needTime" : 0,
								"needFetch" : 0,
								"saveState" : 0,
								"restoreState" : 0,
								"isEOF" : 0,
								"invalidates" : 0,
								"keyPattern" : {
									"_boundToObjectId" : 1
								},
								"indexName" : "_boundToObjectId_1",
								"isMultiKey" : false,
								"direction" : "forward",
								"indexBounds" : {
									"_boundToObjectId" : [
										"[ObjectId('51762b8f78cfa9f357000011'), ObjectId('51762b8f78cfa9f357000011')]"
									]
								},
								"keysExamined" : 1,
								"dupsTested" : 0,
								"dupsDropped" : 0,
								"seenInvalidated" : 0,
								"matchTested" : 0
							},
							{
								"stage" : "IXSCAN",
								"nReturned" : 1,
								"executionTimeMillisEstimate" : 0,
								"works" : 1,
								"advanced" : 1,
								"needTime" : 0,
								"needFetch" : 0,
								"saveState" : 0,
								"restoreState" : 0,
								"isEOF" : 1,
								"invalidates" : 0,
								"keyPattern" : {
									"_teamId" : 1
								},
								"indexName" : "_teamId_1",
								"isMultiKey" : false,
								"direction" : "forward",
								"indexBounds" : {
									"_teamId" : [
										"[true, true]"
									]
								},
								"keysExamined" : 1,
								"dupsTested" : 0,
								"dupsDropped" : 0,
								"seenInvalidated" : 0,
								"matchTested" : 0
							}
						]
					}
				}
			}
		},
		"feedback" : {
			
		},
		"filterSet" : false
	}
]
> db.foo.count({ isQuited: false, _teamId: null, _boundToObjectId: ObjectId("51762b8f78cfa9f357000011") })
9

So we can continue to investigate, would you please reproduce this behavior and provide the log of the query that returns 0 documents at verbosity 5.

Thanks again,
Thomas

Comment by liyang zhou [ 25/May/16 ]

HI Thomas
Thanks for your reply.
We found out that we need the index teamId so we add another index to fix this problem(boundToObjectId_1_teamId_1_isQuited_1)

1.the output of db.members.getIndexes() is:

[
        {
                "v" : 1,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "teambition.members"
        },
        {
                "v" : 1,
                "key" : {
                        "_boundToObjectId" : 1
                },
                "name" : "_boundToObjectId_1",
                "ns" : "teambition.members"
        },
        {
                "v" : 1,
                "key" : {
                        "_userId" : 1,
                        "boundToObjectType" : 1
                },
                "name" : "_userId_1_boundToObjectType_1",
                "ns" : "teambition.members"
        },
        {
                "v" : 1,
                "key" : {
                        "_userId" : 1,
                        "_boundToObjectId" : 1,
                        "isQuited" : 1
                },
                "name" : "_userId_1__boundToObjectId_1_isQuited_1",
                "ns" : "teambition.members"
        },
        {
                "v" : 1,
                "key" : {
                        "_roleId" : 1
                },
                "name" : "_roleId_1",
                "ns" : "teambition.members",
                "background" : 1
        },
        {
                "v" : 1,
                "key" : {
                        "_teamId" : 1
                },
                "name" : "_teamId_1",
                "ns" : "teambition.members",
                "background" : 1
        },
        {
                "v" : 1,
                "key" : {
                        "_boundToObjectId" : 1,
                        "_teamId" : 1,
                        "isQuited" : 1
                },
                "name" : "_boundToObjectId_1__teamId_1_isQuited_1",
                "ns" : "teambition.members",
                "background" : 1
        }                                                

2.the sample of members

{ "_id" : ObjectId("518255a1d654fc833b00009e"), "__v" : 0, "_boundToObjectId" : ObjectId("51824c93d654fc833b00000d"), "_userId" : ObjectId("5138317ecf5183be3d000652"), "boundToObjectType" : "project", "isAdmin" : false, "isOwner" : false, "isQuited" : false, "joined" : ISODate("2013-05-02T12:01:37Z"), "quited" : ISODate("2013-05-30T06:00:26Z"), "role" : 0, "visited" : ISODate("2016-02-19T01:12:24.170Z") }
{ "_id" : ObjectId("51825609d654fc833b0000a0"), "__v" : 0, "_boundToObjectId" : ObjectId("5181d15c6481c01c0900003c"), "_userId" : ObjectId("510cbdfca4b069bb640000a2"), "boundToObjectType" : "organization_project", "isAdmin" : false, "isOwner" : false, "isQuited" : false, "joined" : ISODate("2013-05-02T12:03:21Z"), "quited" : ISODate("2014-05-12T09:25:47.436Z"), "role" : 0, "unreadCount" : 3009, "visited" : ISODate("2016-05-24T06:17:58.859Z"), "_roleId" : null }
{ "_id" : ObjectId("5184535d477315327b00000d"), "__v" : 0, "_boundToObjectId" : ObjectId("5184535d477315327b000007"), "_userId" : ObjectId("5138317ecf5183be3d000652"), "boundToObjectType" : "project", "isAdmin" : true, "isOwner" : true, "isQuited" : false, "joined" : ISODate("2013-05-04T00:16:29Z"), "role" : 2, "visited" : ISODate("2015-07-10T03:14:59.960Z") }
{ "_id" : ObjectId("51875ac2c7235fde27000010"), "__v" : 0, "_boundToObjectId" : ObjectId("5184535d477315327b000007"), "_userId" : ObjectId("5138317ecf5183be3d000652"), "boundToObjectType" : "project", "isAdmin" : false, "isOwner" : false, "isQuited" : true, "joined" : ISODate("2013-05-06T07:24:50Z"), "role" : 0 }
{ "_id" : ObjectId("51877c228638d11929000010"), "__v" : 0, "_boundToObjectId" : ObjectId("51877c228638d1192900000a"), "_userId" : ObjectId("516c078689b7642b0200000a"), "boundToObjectType" : "organization_project", "isAdmin" : true, "isOwner" : true, "isQuited" : false, "joined" : ISODate("2013-05-06T09:47:14Z"), "role" : 2 }
{ "_id" : ObjectId("51877c228638d11929000011"), "__v" : 0, "_boundToObjectId" : ObjectId("51877c228638d1192900000a"), "_userId" : ObjectId("50c32a4be8cf1439d35a67f6"), "boundToObjectType" : "project", "isAdmin" : true, "isOwner" : false, "isQuited" : false, "joined" : ISODate("2013-05-06T09:47:14Z"), "role" : 2 }
{ "_id" : ObjectId("5188e6ce07d92be116000032"), "__v" : 0, "_boundToObjectId" : ObjectId("5188e6ce07d92be11600002c"), "_userId" : ObjectId("50c32a74e8cf1439d35a7274"), "boundToObjectType" : "project", "isAdmin" : true, "isOwner" : true, "isQuited" : false, "joined" : ISODate("2013-05-07T11:34:38Z"), "role" : 2 }
{ "_id" : ObjectId("5188f5e707d92be116000074"), "__v" : 0, "_boundToObjectId" : ObjectId("5188f5e707d92be11600006e"), "_userId" : ObjectId("50ea687e96577816330004fd"), "boundToObjectType" : "project", "isAdmin" : true, "isOwner" : true, "isQuited" : false, "joined" : ISODate("2013-05-07T12:39:03Z"), "role" : 2 }
{ "_id" : ObjectId("5189a74d1f3b8bd6210000b7"), "__v" : 0, "_boundToObjectId" : ObjectId("5188e6ce07d92be11600002c"), "_userId" : ObjectId("50c32aa1e8cf1439d35a7dc8"), "boundToObjectType" : "project", "isAdmin" : false, "isOwner" : false, "isQuited" : false, "joined" : ISODate("2013-05-08T01:15:57Z"), "role" : 0 }
{ "_id" : ObjectId("5189e496dbbd7d433f00003d"), "__v" : 0, "_boundToObjectId" : ObjectId("5189e496dbbd7d433f000037"), "_userId" : ObjectId("50c32a9ce8cf1439d35a7c83"), "boundToObjectType" : "project", "isAdmin" : true, "isOwner" : false, "isQuited" : false, "joined" : ISODate("2013-05-08T05:37:26Z"), "role" : 1, "unreadCount" : 15, "visited" : ISODate("2016-03-15T01:29:34.132Z") }
{ "_id" : ObjectId("54cef81233b1b13706f1a9f2"), "_userId" : ObjectId("54cb6200d1b4c6af47abe570"), "_boundToObjectId" : ObjectId("51762b8f78cfa9f357000011"), "boundToObjectType" : "organization_project", "invited" : ISODate("2015-02-02T04:07:46.732Z"), "role" : 1, "visited" : ISODate("2016-05-24T09:50:29.948Z"), "isQuited" : false, "joined" : ISODate("2015-02-02T04:07:46.732Z"), "__v" : 0, "unreadCount" : 82, "unreadMessageCount" : 23 }
{ "_id" : ObjectId("5518e6489bd6d2e608a12cb2"), "_userId" : ObjectId("50ca97d8f671d2f66c0000cc"), "_boundToObjectId" : ObjectId("51762b8f78cfa9f357000011"), "boundToObjectType" : "organization_project", "invited" : ISODate("2015-03-30T05:59:36.496Z"), "role" : 0, "visited" : ISODate("2016-05-23T03:21:14.617Z"), "isQuited" : false, "joined" : ISODate("2015-03-30T05:59:36.496Z"), "__v" : 0, "unreadCount" : 77, "unreadMessageCount" : 23 }
{ "_id" : ObjectId("556bab484754171a7ae13a2c"), "_userId" : ObjectId("5565b14a12e1b58a77697aaf"), "_boundToObjectId" : ObjectId("51762b8f78cfa9f357000011"), "boundToObjectType" : "organization_project", "invited" : ISODate("2015-06-01T00:46:00.408Z"), "role" : 1, "visited" : ISODate("2016-05-25T06:18:31.663Z"), "isQuited" : false, "joined" : ISODate("2015-06-01T00:46:00.408Z"), "__v" : 0, "_roleId" : null, "unreadCount" : 0, "unreadMessageCount" : 23 }
{ "_id" : ObjectId("55892f06703d099e5dddaf26"), "_boundToObjectId" : ObjectId("51762b8f78cfa9f357000011"), "_userId" : ObjectId("531d127dce55ef66408fdc04"), "boundToObjectType" : "organization_project", "invited" : ISODate("2015-06-23T10:03:50.880Z"), "role" : 0, "isQuited" : false, "joined" : ISODate("2015-06-23T10:03:50.880Z"), "__v" : 0, "visited" : ISODate("2016-05-25T00:42:30.487Z"), "unreadCount" : 46, "unreadMessageCount" : 0 }
{ "_id" : ObjectId("55892f06703d099e5dddaf29"), "_boundToObjectId" : ObjectId("51762b8f78cfa9f357000011"), "_userId" : ObjectId("539e3aacf813b07102bbed16"), "boundToObjectType" : "organization_project", "invited" : ISODate("2015-06-23T10:03:50.885Z"), "role" : 1, "isQuited" : false, "joined" : ISODate("2015-06-23T10:03:50.885Z"), "__v" : 0, "visited" : ISODate("2016-05-25T03:55:02.266Z"), "_roleId" : null, "unreadCount" : 8, "pushStatus" : false, "unreadMessageCount" : 0 }
{ "_id" : ObjectId("55892f06703d099e5dddaf27"), "_boundToObjectId" : ObjectId("51762b8f78cfa9f357000011"), "_userId" : ObjectId("5400459d76a6360e22748817"), "boundToObjectType" : "organization_project", "invited" : ISODate("2015-06-23T10:03:50.883Z"), "role" : 0, "isQuited" : false, "joined" : ISODate("2015-06-23T10:03:50.883Z"), "__v" : 0, "visited" : ISODate("2016-05-25T06:31:30.852Z"), "unreadCount" : 100, "unreadMessageCount" : 0 }
{ "_id" : ObjectId("55892f06703d099e5dddaf31"), "_boundToObjectId" : ObjectId("51762b8f78cfa9f357000011"), "_userId" : ObjectId("5550a133fe2563dd4da0f447"), "boundToObjectType" : "organization_project", "invited" : ISODate("2015-06-23T10:03:50.895Z"), "role" : 0, "isQuited" : false, "joined" : ISODate("2015-06-23T10:03:50.895Z"), "__v" : 0, "visited" : ISODate("2016-05-20T16:37:26.958Z"), "unreadCount" : 380, "unreadMessageCount" : 0 }
{ "_id" : ObjectId("55892f06703d099e5dddaf30"), "_boundToObjectId" : ObjectId("51762b8f78cfa9f357000011"), "_userId" : ObjectId("546949db6a3b96107f3a7855"), "boundToObjectType" : "organization_project", "invited" : ISODate("2015-06-23T10:03:50.894Z"), "role" : 0, "isQuited" : false, "joined" : ISODate("2015-06-23T10:03:50.894Z"), "__v" : 0, "visited" : ISODate("2016-05-24T07:45:34.223Z"), "unreadCount" : 17, "unreadMessageCount" : 23, "pushStatus" : false }
{ "_id" : ObjectId("55892f06703d099e5dddaf2d"), "_boundToObjectId" : ObjectId("51762b8f78cfa9f357000011"), "_userId" : ObjectId("50c32a4be8cf1439d35a67fc"), "boundToObjectType" : "organization_project", "invited" : ISODate("2015-06-23T10:03:50.891Z"), "role" : 2, "isQuited" : false, "joined" : ISODate("2015-06-23T10:03:50.891Z"), "__v" : 0, "visited" : ISODate("2016-05-25T05:09:41.930Z"), "_roleId" : null, "unreadCount" : 0, "pushStatus" : true, "unreadMessageCount" : 0 } 

3.I can get the log right now
i`ll upload later.
And now the query will use the new index we added,won`t return 0.

Comment by Kelsey Schubert [ 19/May/16 ]

Hi 378013446@qq.com,

Thanks for reporting this behavior. I'm glad you were able to identify a workaround, but we'd like to investigate this issue further. Are you able to construct a simple reproduction that we can use?

If not, after rebuilding the dropped index and reproducing this issue, would you please provide the following information to help us investigate this issue?

  1. output of db.members.getIndexes()
  2. sample documents from the members collections, including one of the 76 documents returned by the query.
  3. log output of queries
    1. please set the query log verbosity to level 5 by executing db.setLogLevel(5, "query")
    2. provide the logs for the query that does not return any results
    3. after clearing the cache, please provide the logs for the query that returns 76 results
    4. you may revert the verbosity settings to the default values by executing db.setLogLevel(-1, "query")

Thank you for your help,
Thomas

Comment by liyang zhou [ 19/May/16 ]

well.we`ve already find the solution.

The query PlanCache using the index _teamId_1
when using this index,the index indexBounds will not include "null"!
this query should use index _boundToObjectId_1.
so we drop the index _teamId_1.

Comment by liyang zhou [ 19/May/16 ]

The indexes of thie collection is:
"indexSizes" :

{ "_id_" : 48005120, "_boundToObjectId_1" : 33914880, "_userId_1_boundToObjectType_1" : 47042560, "_teamId_1" : 524288, "_userId_1__boundToObjectId_1_isQuited_1" : 83439616, "_roleId_1" : 14401536 }

,

and we do not have any Sparse indexes

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