[SERVER-11821] Indexes are ignored with use of $not in 2.5, obeyed in 2.4 Created: 21/Nov/13  Updated: 11/Jul/16  Resolved: 04/Dec/13

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

Type: Bug Priority: Major - P3
Reporter: Luke Lovett Assignee: Benety Goh
Resolution: Done Votes: 0
Labels: 26qa, nqf, query_triage
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

> db.serverBuildInfo()
{
"version" : "2.5.5-pre-",
"gitVersion" : "9795d501bb74edcfbcd518300fd1b96e4f2856e1",
"OpenSSLVersion" : "",
"sysInfo" : "Darwin bs-osx-106-x86-64-2.10gen.cc 10.8.0 Darwin Kernel Version 10.8.0: Tue Jun 7 16:32:41 PDT 2011; root:xnu-1504.15.3~1/RELEASE_X86_64 x86_64 BOOST_LIB_VERSION=1_49",
"loaderFlags" : "-fPIC -pthread -Wl,-bind_at_load -m64 -mmacosx-version-min=10.6",
"compilerFlags" : "-Wnon-virtual-dtor -Woverloaded-virtual -fPIC -fno-strict-aliasing -ggdb -pthread -Wall -Wsign-compare -Wno-unknown-pragmas -Winvalid-pch -Werror -pipe -O3 -m64 -Wno-unused-function -Wno-deprecated-declarations -mmacosx-version-min=10.6",
"allocator" : "system",
"versionArray" : [
2,
5,
5,
-100
],
"javascriptEngine" : "V8",
"bits" : 64,
"debug" : false,
"maxBsonObjectSize" : 16777216,
"ok" : 1
}
and
> db.serverBuildInfo()
{
"version" : "2.4.9-pre-",
"gitVersion" : "5779b6e198c0dd22a99e12837faea4b5e8b2664f",
"sysInfo" : "Darwin bs-osx-106-x86-64-2.10gen.cc 10.8.0 Darwin Kernel Version 10.8.0: Tue Jun 7 16:32:41 PDT 2011; root:xnu-1504.15.3~1/RELEASE_X86_64 x86_64 BOOST_LIB_VERSION=1_49",
"loaderFlags" : "-fPIC -pthread -rdynamic -m64",
"compilerFlags" : "-Wnon-virtual-dtor -Woverloaded-virtual -fPIC -fno-strict-aliasing -ggdb -pthread -Wall -Wsign-compare -Wno-unknown-pragmas -Winvalid-pch -O3 -m64",
"allocator" : "system",
"versionArray" : [
2,
4,
9,
-100
],
"javascriptEngine" : "V8",
"bits" : 64,
"debug" : false,
"maxBsonObjectSize" : 16777216,
"ok" : 1
}


Attachments: File server11821.js     File server11821.js    
Issue Links:
Depends
depends on SERVER-11446 don't punt all not/nor to collscan. Closed
Related
Backwards Compatibility: Fully Compatible
Operating System: ALL
Steps To Reproduce:

2.5.5-pre-

> db.c.insert({a:1, b:2})
Insert WriteResult({ "ok" : 1, "n" : 1 })
> db.c.ensureIndex({a:1})
> db.c.find({b:{$not:{$lt:-1000}}}).hint({a:1}).explain()
{
	"cursor" : "BasicCursor",
	"n" : 1,
	"nscannedObjects" : 1,
	"nscanned" : 1,
	"nscannedObjectsAllPlans" : 1,
	"nscannedAllPlans" : 1,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"server" : "llmac:28000"
}
> db.c.find({b:{$not:{$lt:-1000}}}).hint({notAnIndexAtAll:1}).explain()
{
	"cursor" : "BasicCursor",
	"n" : 1,
	"nscannedObjects" : 1,
	"nscanned" : 1,
	"nscannedObjectsAllPlans" : 1,
	"nscannedAllPlans" : 1,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"server" : "llmac:28000"
}
> db.c.find({a:{$not:{$gt:2}}}).explain()
{
	"cursor" : "BasicCursor",
	"n" : 1,
	"nscannedObjects" : 1,
	"nscanned" : 1,
	"nscannedObjectsAllPlans" : 1,
	"nscannedAllPlans" : 1,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"server" : "llmac:28000"
}

2.4.9-pre-

> db.c.insert({a:1, b:2})
> db.c.ensureIndex({a:1})
> db.c.find({b:{$not:{$lt:-1000}}}).hint({a:1}).explain()
{
	"cursor" : "BtreeCursor a_1",
	"isMultiKey" : false,
	"n" : 1,
	"nscannedObjects" : 1,
	"nscanned" : 1,
	"nscannedObjectsAllPlans" : 1,
	"nscannedAllPlans" : 1,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"a" : [
			[
				{
					"$minElement" : 1
				},
				{
					"$maxElement" : 1
				}
			]
		]
	},
	"server" : "llmac:27017"
}
> db.c.find({b:{$not:{$lt:-1000}}}).hint({notAnIndexAtAll:1}).explain()
2013-11-21T23:35:45.395+0000 error: { "$err" : "bad hint", "code" : 10113 } at src/mongo/shell/query.js:131
> db.c.find({a:{$not:{$gt:2}}}).explain()
{
	"cursor" : "BtreeCursor a_1",
	"isMultiKey" : false,
	"n" : 1,
	"nscannedObjects" : 1,
	"nscanned" : 1,
	"nscannedObjectsAllPlans" : 1,
	"nscannedAllPlans" : 1,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"a" : [
			[
				-1.7976931348623157e+308,
				2
			]
		]
	},
	"server" : "llmac:27017"
}

Participants:

 Description   

Indexes seem to be ignored when $not is in the query in version 2.5 of mongodb. Version 2.4 seems to still use an index if available or given a hint. 2.5 seems to ignore hints entirely in the presence of $not. This leads to different results turning up in queries as well as some different error conditions.



 Comments   
Comment by Luke Lovett [ 03/Dec/13 ]

It looks as though this issue can also be reproduced using $nin, with the exception that neither version uses an index by default (i.e., without a hint). 2.5 won't use the index even with the hint.

2.5.5-pre-

> db.c.insert({a:1})
Insert WriteResult({ "ok" : 1, "n" : 1 })
> db.c.insert({a:2})
Insert WriteResult({ "ok" : 1, "n" : 1 })
> db.c.ensureIndex({a:1})
> db.c.find({a:{$nin:[2,3,4]}}).explain()
{
	"cursor" : "BasicCursor",
	"n" : 1,
	"nscannedObjects" : 2,
	"nscanned" : 2,
	"nscannedObjectsAllPlans" : 2,
	"nscannedAllPlans" : 2,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"server" : "10gencasvr:28000"
}
> db.c.find({a:{$nin:[2,3,4]}}).hint({a:1}).explain()
{
	"cursor" : "BasicCursor",
	"n" : 1,
	"nscannedObjects" : 2,
	"nscanned" : 2,
	"nscannedObjectsAllPlans" : 2,
	"nscannedAllPlans" : 2,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"server" : "10gencasvr:28000"
}
> db.c.find({a:{$nin:[2,3,4]}}).hint({b:1}).explain()
{
	"cursor" : "BasicCursor",
	"n" : 1,
	"nscannedObjects" : 2,
	"nscanned" : 2,
	"nscannedObjectsAllPlans" : 2,
	"nscannedAllPlans" : 2,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"server" : "10gencasvr:28000"
}

2.4.9-pre-

> db.c.insert({a:1})
> db.c.insert({a:2})
> db.c.ensureIndex({a:1})
> db.c.find({a:{$nin:[2,3,4]}}).explain()
{
	"cursor" : "BasicCursor",
	"isMultiKey" : false,
	"n" : 1,
	"nscannedObjects" : 2,
	"nscanned" : 2,
	"nscannedObjectsAllPlans" : 2,
	"nscannedAllPlans" : 2,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
 
	},
	"server" : "10gencasvr:27017"
}
> db.c.find({a:{$nin:[2,3,4]}}).hint({a:1}).explain()
{
	"cursor" : "BtreeCursor a_1",
	"isMultiKey" : false,
	"n" : 1,
	"nscannedObjects" : 2,
	"nscanned" : 2,
	"nscannedObjectsAllPlans" : 2,
	"nscannedAllPlans" : 2,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"a" : [
			[
				{
					"$minElement" : 1
				},
				{
					"$maxElement" : 1
				}
			]
		]
	},
	"server" : "10gencasvr:27017"
}
> db.c.find({a:{$nin:[2,3,4]}}).hint({b:1}).explain()
Tue Dec  3 13:20:31.836 error: { "$err" : "bad hint", "code" : 10113 } at src/mongo/shell/query.js:128

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