Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-11821

Indexes are ignored with use of $not in 2.5, obeyed in 2.4

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • 2.5.5
    • Affects Version/s: None
    • Component/s: Querying
    • Environment:
    • Fully Compatible
    • ALL
    • Hide

      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"
}
      Show
      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" }

      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.

        1. server11821.js
          0.9 kB
        2. server11821.js
          1 kB

            Assignee:
            benety.goh@mongodb.com Benety Goh
            Reporter:
            luke.lovett Luke Lovett
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated:
              Resolved: