[SERVER-16256] $all clause with elemMatch uses wider bounds than needed Created: 20/Nov/14  Updated: 29/Dec/17  Resolved: 01/Dec/14

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.6.5
Fix Version/s: 2.6.10, 2.8.0-rc2

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

Issue Links:
Depends
Duplicate
is duplicated by SERVER-17221 Query on key-val array using $all and... Closed
Related
related to SERVER-19999 $all with $elemMatch indexes first el... Closed
Operating System: ALL
Backport Completed:
Participants:
Case:

 Description   

When using $all with $elemMatch criteria, the query uses wider bounds than what it should have used (v2.4 behaves correctly in selecting narrow bounds). Following are steps indicates the wider bounds being queried on the array field "bc":

db.x.drop();
db.x.ensureIndex({a: 1, "dims.b": 1, "dims.c": 1});
db.x.insert({a: 1, bc: [{b: "abc", c: "xyz"}, {b: "gsh", c: "ysh"]});
db.x.find({
        "a" : 1,
        "bc" : {
                "$all" : [
                        {
                                "$elemMatch" : {
                                        "b" : "pl",
                                        "c" : "tgit"
                                }
                        },
                        {
                                "$elemMatch" : {
                                        "b" : "xy",
                                        "c" : "ak"
                                        }
                         }
                ]
        }
}).explain();

Explain with $all:

{
	"cursor" : "BtreeCursor a_1_bc.b_1_bc.c_1",
	"isMultiKey" : true,
	"n" : 0,
	"nscannedObjects" : 2,
	"nscanned" : 3,
	"nscannedObjectsAllPlans" : 2,
	"nscannedAllPlans" : 3,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"a" : [
			[
				1,
				1
			]
		],
		"bc.b" : [
			[
				{
					"$minElement" : 1
				},
				{
					"$maxElement" : 1
				}
			]
		],
		"bc.c" : [
			[
				{
					"$minElement" : 1
				},
				{
					"$maxElement" : 1
				}
			]
		]
	},
	"filterSet" : false
}



 Comments   
Comment by Githook User [ 10/Apr/15 ]

Author:

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

Message: SERVER-16256 get rid of ALL match expressions

Every $all should parse to an AND match expression. This simplifies the
implementation, and fixes a bug related to bounds building for $all-$elemMatch
constructions.

(cherry picked from commit 430f62da7380a6864058c2f75a64c5d662a95176)

Conflicts:
src/mongo/db/matcher/expression_array.cpp
src/mongo/db/matcher/expression_array.h
src/mongo/db/query/query_planner_test.cpp
src/mongo/s/chunk_manager_targeter_test.cpp
Branch: v2.6
https://github.com/mongodb/mongo/commit/bf2d5d7401557ffaccc14f2e68a2f8207841fce4

Comment by Githook User [ 01/Dec/14 ]

Author:

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

Message: SERVER-16256 get rid of ALL match expressions

Every $all should parse to an AND match expression. This simplifies the
implementation, and fixes a bug related to bounds building for $all-$elemMatch
constructions.
Branch: master
https://github.com/mongodb/mongo/commit/430f62da7380a6864058c2f75a64c5d662a95176

Comment by David Storch [ 20/Nov/14 ]

Simpler repro:

t.drop();
t.ensureIndex({d: 1, "a.b": 1, "a.c": 1});
t.find({d: 1, a: {$all: [{$elemMatch: {b: 1, c: 1}}]}}).explain();

Comment by Anil Kumar [ 20/Nov/14 ]

Using an alternate for with "$and" uses the appropriate narrow bound selection:

db.x.find({
        "a" : 1,
        "$and" : [ {"bc": {
                                "$elemMatch" : {
                                        "b" : "pl",
                                        "c" : "tgit"
                                }
                        } },
                    {"bc": {
                                "$elemMatch" : {
                                        "b" : "xy",
                                        "c" : "ak"
                                        }
                                }
                        }
                ]
        }
).explain();

Explain Plan with $and:

{
	"cursor" : "BtreeCursor a_1_bc.b_1_bc.c_1",
	"isMultiKey" : true,
	"n" : 0,
	"nscannedObjects" : 0,
	"nscanned" : 0,
	"nscannedObjectsAllPlans" : 0,
	"nscannedAllPlans" : 0,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"a" : [
			[
				1,
				1
			]
		],
		"bc.b" : [
			[
				"pl",
				"pl"
			]
		],
		"bc.c" : [
			[
				"tgit",
				"tgit"
			]
		]
	},
	"filterSet" : false
}

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