[SERVER-25555] Incorrect result for query that use compound multikey-index on an array field of embedded documents Created: 11/Aug/16  Updated: 11/Aug/16  Resolved: 11/Aug/16

Status: Closed
Project: Core Server
Component/s: Aggregation Framework, Querying
Affects Version/s: 3.0.12, 3.2.8
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: David Durcak Assignee: Unassigned
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Steps To Reproduce:

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
//}

Participants:

 Description   

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,



 Comments   
Comment by Charlie Swanson [ 11/Aug/16 ]

heloro37,

This is the expected behavior. $elemMatch is a query predicate that matches elements within an array, but with your second query

{ 'ratings.score' : { $elemMatch: { 'q1' : 4, 'q2' : 9}}}

'ratings.score' does not specify an array. 'ratings' is the array, 'ratings.score' is the elements within the array.

I believe this simpler example might help illuminate it:

replset:PRIMARY> db.foo.drop()
true
replset:PRIMARY> db.foo.insert({x: [{y: 1}, {y: 2}]})
WriteResult({ "nInserted" : 1 })
replset:PRIMARY> db.foo.insert({x: {y: 1}})
WriteResult({ "nInserted" : 1 })
replset:PRIMARY> db.foo.find({x: {y: 1}})
{ "_id" : ObjectId("57ac9870f50bb0c00a00570e"), "x" : [ { "y" : 1 }, { "y" : 2 } ] }
{ "_id" : ObjectId("57ac9877f50bb0c00a00570f"), "x" : { "y" : 1 } }
replset:PRIMARY> db.foo.find({x: {$elemMatch: {y: 1}}})
{ "_id" : ObjectId("57ac9870f50bb0c00a00570e"), "x" : [ { "y" : 1 }, { "y" : 2 } ] }

It's a little weird, but our semantics are to apply the query predicate ({$elemMatch: {q1: 4, q2: 9}} in query2's case) to every element within any arrays encountered. Thus we will apply the $elemMatch to things which are not actually arrays, causing the documents not to match.

If these are really the only fields in your document, you could get away with modifying query1 to be an exact match:

{ratings: {score: {q1: 4, q2: 9}}}

It's also worth noting that query1, without modification, should generate tighter bounds on 3.4 due to the resolution of SERVER-15086.

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