Loading...

Type: Bug
Resolution: Done
Priority: Major - P3
Fix Version/s: None
Affects Version/s: 3.0.12, 3.2.8
Component/s: Aggregation Framework, Querying
Labels:
None

Operating System:
ALL

Steps To Reproduce:

Hide

var colName = 'mycol';
var dbTest = db[colName];
var docSize = 10000;

function generateData(dbTest, docSize) {	
	while (docSize--) {	
		var doc = {
				'item' : docSize,
				'ratings' : generateRatings(20)
		};
		dbTest.insert(doc);
	}

	function generateRatings(maxRatings) {
		var rSize = rand(maxRatings);
		if (rSize) {
			var ratings = [];
			while (rSize--) {
				ratings.push({
					'revId': new ObjectId(),
					'score': {
						'q1': rand(6),
						'q2': rand(15)
					}
				});
			}
			return ratings;
		}
	}
}

// return number from interval < 0 , topLimit-1 >
function rand(topLimit) {
	return Math.floor(Math.random() * topLimit);
}



generateData(dbTest, docSize);
dbTest.createIndex({'ratings.score.q1':1, 'ratings.score.q2':1});
// TEST:

var q1 = 4;
var q2 = 9;
var query1 = { 'ratings' : { $elemMatch : { 'score.q1' : q1 , 'score.q2' : q2}}};
var query2 = { 'ratings.score' : { $elemMatch : { 'q1' : q1, 'q2' : q2}}};

dbTest.count(query1); // Use index only for 'ratings.score.q1'. Return X.
dbTest.count(query2); // Use index for q1 and q2. Index match X documents, but always return 0. 

dbTest.explain({'executionStats':1}).count(query1);
dbTest.explain({'executionStats':1}).count(query2); 


//> // TEST:
//> 
//> var q1 = 4;
//> var q2 = 9;
//> var query1 = { 'ratings' : { $elemMatch : { 'score.q1' : q1 , 'score.q2' : q2}}};
//> var query2 = { 'ratings.score' : { $elemMatch : { 'q1' : q1, 'q2' : q2}}};
//> 
//> dbTest.count(query1); // Use index only for 'ratings.score.q1'. Return X.
//981
//> dbTest.count(query2); // Use index for q1 and q2. Index match X documents, but always return 0. 
//0
//> 
//> dbTest.explain({'executionStats':1}).count(query1);
//{
//	"queryPlanner" : {
//		"plannerVersion" : 1,
//		"namespace" : "ttt.mycol",
//		"indexFilterSet" : false,
//		"parsedQuery" : {
//			"ratings" : {
//				"$elemMatch" : {
//					"$and" : [
//						{
//							"score.q1" : {
//								"$eq" : 4
//							}
//						},
//						{
//							"score.q2" : {
//								"$eq" : 9
//							}
//						}
//					]
//				}
//			}
//		},
//		"winningPlan" : {
//			"stage" : "COUNT",
//			"inputStage" : {
//				"stage" : "KEEP_MUTATIONS",
//				"inputStage" : {
//					"stage" : "FETCH",
//					"filter" : {
//						"ratings" : {
//							"$elemMatch" : {
//								"$and" : [
//									{
//										"score.q1" : {
//											"$eq" : 4
//										}
//									},
//									{
//										"score.q2" : {
//											"$eq" : 9
//										}
//									}
//								]
//							}
//						}
//					},
//					"inputStage" : {
//						"stage" : "IXSCAN",
//						"keyPattern" : {
//							"ratings.score.q1" : 1,
//							"ratings.score.q2" : 1
//						},
//						"indexName" : "ratings.score.q1_1_ratings.score.q2_1",
//						"isMultiKey" : true,
//						"direction" : "forward",
//						"indexBounds" : {
//							"ratings.score.q1" : [
//								"[4.0, 4.0]"
//							],
//							"ratings.score.q2" : [
//								"[MinKey, MaxKey]"
//							]
//						}
//					}
//				}
//			}
//		},
//		"rejectedPlans" : [ ]
//	},
//	"executionStats" : {
//		"executionSuccess" : true,
//		"nReturned" : 0,
//		"executionTimeMillis" : 30,
//		"totalKeysExamined" : 14919,
//		"totalDocsExamined" : 7120,
//		"executionStages" : {
//			"stage" : "COUNT",
//			"nReturned" : 0,
//			"executionTimeMillisEstimate" : 30,
//			"works" : 14920,
//			"advanced" : 0,
//			"needTime" : 14919,
//			"needFetch" : 0,
//			"saveState" : 116,
//			"restoreState" : 116,
//			"isEOF" : 1,
//			"invalidates" : 0,
//			"nCounted" : 981,
//			"nSkipped" : 0,
//			"inputStage" : {
//				"stage" : "KEEP_MUTATIONS",
//				"nReturned" : 981,
//				"executionTimeMillisEstimate" : 30,
//				"works" : 14920,
//				"advanced" : 981,
//				"needTime" : 13938,
//				"needFetch" : 0,
//				"saveState" : 116,
//				"restoreState" : 116,
//				"isEOF" : 1,
//				"invalidates" : 0,
//				"inputStage" : {
//					"stage" : "FETCH",
//					"filter" : {
//						"ratings" : {
//							"$elemMatch" : {
//								"$and" : [
//									{
//										"score.q1" : {
//											"$eq" : 4
//										}
//									},
//									{
//										"score.q2" : {
//											"$eq" : 9
//										}
//									}
//								]
//							}
//						}
//					},
//					"nReturned" : 981,
//					"executionTimeMillisEstimate" : 30,
//					"works" : 14920,
//					"advanced" : 981,
//					"needTime" : 13938,
//					"needFetch" : 0,
//					"saveState" : 116,
//					"restoreState" : 116,
//					"isEOF" : 1,
//					"invalidates" : 0,
//					"docsExamined" : 7120,
//					"alreadyHasObj" : 0,
//					"inputStage" : {
//						"stage" : "IXSCAN",
//						"nReturned" : 7120,
//						"executionTimeMillisEstimate" : 10,
//						"works" : 14920,
//						"advanced" : 7120,
//						"needTime" : 7799,
//						"needFetch" : 0,
//						"saveState" : 116,
//						"restoreState" : 116,
//						"isEOF" : 1,
//						"invalidates" : 0,
//						"keyPattern" : {
//							"ratings.score.q1" : 1,
//							"ratings.score.q2" : 1
//						},
//						"indexName" : "ratings.score.q1_1_ratings.score.q2_1",
//						"isMultiKey" : true,
//						"direction" : "forward",
//						"indexBounds" : {
//							"ratings.score.q1" : [
//								"[4.0, 4.0]"
//							],
//							"ratings.score.q2" : [
//								"[MinKey, MaxKey]"
//							]
//						},
//						"keysExamined" : 14919,
//						"dupsTested" : 14919,
//						"dupsDropped" : 7799,
//						"seenInvalidated" : 0,
//						"matchTested" : 0
//					}
//				}
//			}
//		},
//		"allPlansExecution" : [ ]
//	},
//	"serverInfo" : {
//		"host" : "durcakubupc",
//		"port" : 27017,
//		"version" : "3.0.12",
//		"gitVersion" : "33934938e0e95d534cebbaff656cde916b9c3573"
//	},
//	"ok" : 1
//}
//> dbTest.explain({'executionStats':1}).count(query2); 
//{
//	"queryPlanner" : {
//		"plannerVersion" : 1,
//		"namespace" : "ttt.mycol",
//		"indexFilterSet" : false,
//		"parsedQuery" : {
//			"ratings.score" : {
//				"$elemMatch" : {
//					"$and" : [
//						{
//							"q1" : {
//								"$eq" : 4
//							}
//						},
//						{
//							"q2" : {
//								"$eq" : 9
//							}
//						}
//					]
//				}
//			}
//		},
//		"winningPlan" : {
//			"stage" : "COUNT",
//			"inputStage" : {
//				"stage" : "KEEP_MUTATIONS",
//				"inputStage" : {
//					"stage" : "FETCH",
//					"filter" : {
//						"ratings.score" : {
//							"$elemMatch" : {
//								"$and" : [
//									{
//										"q1" : {
//											"$eq" : 4
//										}
//									},
//									{
//										"q2" : {
//											"$eq" : 9
//										}
//									}
//								]
//							}
//						}
//					},
//					"inputStage" : {
//						"stage" : "IXSCAN",
//						"keyPattern" : {
//							"ratings.score.q1" : 1,
//							"ratings.score.q2" : 1
//						},
//						"indexName" : "ratings.score.q1_1_ratings.score.q2_1",
//						"isMultiKey" : true,
//						"direction" : "forward",
//						"indexBounds" : {
//							"ratings.score.q1" : [
//								"[4.0, 4.0]"
//							],
//							"ratings.score.q2" : [
//								"[9.0, 9.0]"
//							]
//						}
//					}
//				}
//			}
//		},
//		"rejectedPlans" : [ ]
//	},
//	"executionStats" : {
//		"executionSuccess" : true,
//		"nReturned" : 0,
//		"executionTimeMillis" : 3,
//		"totalKeysExamined" : 981,
//		"totalDocsExamined" : 981,
//		"executionStages" : {
//			"stage" : "COUNT",
//			"nReturned" : 0,
//			"executionTimeMillisEstimate" : 10,
//			"works" : 982,
//			"advanced" : 0,
//			"needTime" : 981,
//			"needFetch" : 0,
//			"saveState" : 7,
//			"restoreState" : 7,
//			"isEOF" : 1,
//			"invalidates" : 0,
//			"nCounted" : 0,
//			"nSkipped" : 0,
//			"inputStage" : {
//				"stage" : "KEEP_MUTATIONS",
//				"nReturned" : 0,
//				"executionTimeMillisEstimate" : 10,
//				"works" : 982,
//				"advanced" : 0,
//				"needTime" : 981,
//				"needFetch" : 0,
//				"saveState" : 7,
//				"restoreState" : 7,
//				"isEOF" : 1,
//				"invalidates" : 0,
//				"inputStage" : {
//					"stage" : "FETCH",
//					"filter" : {
//						"ratings.score" : {
//							"$elemMatch" : {
//								"$and" : [
//									{
//										"q1" : {
//											"$eq" : 4
//										}
//									},
//									{
//										"q2" : {
//											"$eq" : 9
//										}
//									}
//								]
//							}
//						}
//					},
//					"nReturned" : 0,
//					"executionTimeMillisEstimate" : 10,
//					"works" : 982,
//					"advanced" : 0,
//					"needTime" : 981,
//					"needFetch" : 0,
//					"saveState" : 7,
//					"restoreState" : 7,
//					"isEOF" : 1,
//					"invalidates" : 0,
//					"docsExamined" : 981,
//					"alreadyHasObj" : 0,
//					"inputStage" : {
//						"stage" : "IXSCAN",
//						"nReturned" : 981,
//						"executionTimeMillisEstimate" : 0,
//						"works" : 982,
//						"advanced" : 981,
//						"needTime" : 0,
//						"needFetch" : 0,
//						"saveState" : 7,
//						"restoreState" : 7,
//						"isEOF" : 1,
//						"invalidates" : 0,
//						"keyPattern" : {
//							"ratings.score.q1" : 1,
//							"ratings.score.q2" : 1
//						},
//						"indexName" : "ratings.score.q1_1_ratings.score.q2_1",
//						"isMultiKey" : true,
//						"direction" : "forward",
//						"indexBounds" : {
//							"ratings.score.q1" : [
//								"[4.0, 4.0]"
//							],
//							"ratings.score.q2" : [
//								"[9.0, 9.0]"
//							]
//						},
//						"keysExamined" : 981,
//						"dupsTested" : 981,
//						"dupsDropped" : 0,
//						"seenInvalidated" : 0,
//						"matchTested" : 0
//					}
//				}
//			}
//		},
//		"allPlansExecution" : [ ]
//	},
//	"serverInfo" : {
//		"host" : "durcakubupc",
//		"port" : 27017,
//		"version" : "3.0.12",
//		"gitVersion" : "33934938e0e95d534cebbaff656cde916b9c3573"
//	},
//	"ok" : 1
//}

Show

Confidence Status:
None
Work Order:
3

Aha! Reference:
None
Tracking Level:
None
Risk Status:
None
Exec Notes:
None
Goal Name:
None
Goal Link:
None

According documentation (https://docs.mongodb.com/manual/core/multikey-index-bounds/#elemmatch-on-incomplete-path):

First query use only one index bound and return correct result X. Second query use both index bounds and exactly X documents is matched and examined, but result is 0 instead of X.

index: {'ratings.score.q1':1, 'ratings.score.q2':1}
query1 : { 'ratings' : { $elemMatch : { 'score.q1' : 4 , 'score.q2' : 9}}};
query2 : { 'ratings.score' : { $elemMatch : { 'q1' : 4, 'q2' : 9}}};

data example:

{
	"_id" : ObjectId("57ac70613d03cd753298656d"),
	"item" : 9999,
	"ratings" : [
		{
			"revId" : ObjectId("57ac70613d03cd753298656b"),
			"score" : {
				"q1" : 5,
				"q2" : 2
			}
		},
		{
			"revId" : ObjectId("57ac70613d03cd753298656c"),
			"score" : {
				"q1" : 4,
				"q2" : 9
			}
		}
	]
}

query1;

"indexBounds" : {
	"ratings.score.q1" : [
		"[4.0, 4.0]"
	],
	"ratings.score.q2" : [
		"[MinKey, MaxKey]"
	]
}
"executionStats" : {
	"executionSuccess" : true,
	"nReturned" : 0,
	"executionTimeMillis" : 30,
	"totalKeysExamined" : 14919,
	"totalDocsExamined" : 7120,

query2:

"indexBounds" : {
	"ratings.score.q1" : [
		"[4.0, 4.0]"
	],
	"ratings.score.q2" : [
		"[9.0, 9.0]"
	]
}
"executionStats" : {
	"executionSuccess" : true,
	"nReturned" : 0,
	"executionTimeMillis" : 3,
	"totalKeysExamined" : 981,
	"totalDocsExamined" : 981,

Details

Description

Attachments

Activity

People

Dates