[SERVER-14718] Plan for query with negation predicates below an $elemMatch value not considered Created: 29/Jul/14  Updated: 14/Nov/14  Resolved: 09/Aug/14

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.6.0, 2.6.3
Fix Version/s: 2.7.5

Type: Bug Priority: Major - P3
Reporter: Jonathan Abrahams Assignee: David Storch
Resolution: Done Votes: 0
Labels: 26qa
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Related
Backwards Compatibility: Fully Compatible
Operating System: ALL
Steps To Reproduce:

 
 
db.elem.insert({arr: [ 1, 2, 3], a: {arr: [9,8,7]}, b:3 })
db.elem.insert({arr: [ 4,5,6], a: {arr: [19,18,17]}, b:5 })
db.elem.ensureIndex({arr:1})
db.elem.ensureIndex({"a.arr":1})
db.elem.ensureIndex({b:1})
db.elem.ensureIndex({arr:1, b: 1})
db.elem.find({b: {$gt: 3}, arr: {$elemMatch:{$gt: 5}}}).explain(true)
db.elem.find({b: {$gt: 3}, arr: {$elemMatch:{$ne: 5}}}).explain(true)

Participants:

 Description   

Part of the query planner's index selection phase is to collect all paths over which the query has predicates (see the mongo::QueryPlannerIXSelect::getFields function). The implementation ignores negation predicates inside an $elemMatch value expression. The consequence is that the planner may fail to generate plans which index negation predicates inside $elemMatch value.

Original Description

A query containing an $elemMatch with negation predicates is not considereding all plans.

The Plan with an index which has an array, which the query includes a $elemMatch with a negation predicate is not considered. A similar query without the negation predicate does include the Plans.

Note that only the plan for b index is considered (see second query result from the steps)



 Comments   
Comment by Githook User [ 09/Aug/14 ]

Author:

{u'username': u'dstorch', u'name': u'David Storch', u'email': u'david.storch@10gen.com'}

Message: SERVER-14718 index negations below elemMatch value
Branch: master
https://github.com/mongodb/mongo/commit/4f5703ae45f05576fac5261dc985aabd142a0a78

Comment by David Storch [ 29/Jul/14 ]

Here is a simpler way to reproduce this issue:

> db.adminCommand({setParameter: 1, logComponentVerbosity: {query: {verbosity: 5}}});
> db.elem.drop()
> db.elem.insert({arr: [1, 2, 3]})
> db.elem.ensureIndex({arr: 1})
> db.elem.find({arr: {$elemMatch: {$ne: 4}}}).explain(true);

The explain output shows that the planner falls back to a collection scan rather than using an indexed plan:

{
	"cursor" : "BasicCursor",
	"isMultiKey" : false,
	"n" : 1,
	"nscannedObjects" : 1,
	"nscanned" : 1,
	"nscannedObjectsAllPlans" : 1,
	"nscannedAllPlans" : 1,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"allPlans" : [
		{
			"cursor" : "BasicCursor",
			"isMultiKey" : false,
			"n" : 1,
			"nscannedObjects" : 1,
			"nscanned" : 1,
			"scanAndOrder" : false,
			"indexOnly" : false,
			"nChunkSkips" : 0
		}
	],
	"server" : "Macintosh.local:27017",
	"stats" : {
		"type" : "COLLSCAN",
		"works" : 3,
		"yields" : 0,
		"unyields" : 0,
		"invalidates" : 0,
		"advanced" : 1,
		"needTime" : 1,
		"isEOF" : 1,
		"docsTested" : 1,
		"children" : [ ]
	}
}

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