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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.5.5
    • Component/s: Querying
    • Environment:
    • Backwards Compatibility:
      Fully Compatible
    • Operating System:
      ALL
    • Steps To Reproduce:
      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" }

      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.

        Attachments

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

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: