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

Query with min/max modifier uses hashed index

    • Type: Icon: Bug Bug
    • Resolution: Fixed
    • Priority: Icon: Critical - P2 Critical - P2
    • 4.1.11
    • Affects Version/s: 4.0.4
    • Component/s: Querying
    • None
    • Minor Change
    • ALL
    • Hide
      1. Install standalone replicaset (node-1);
      2. Create collection with hashed _id index:
      rs0:PRIMARY> use foo
      switched to db foo
      rs0:PRIMARY> db.bar.insert({_id: "test"})
      WriteResult({ "nInserted" : 1 })
      rs0:PRIMARY> db.bar.createIndex({_id: "hashed"})
      {
      	"createdCollectionAutomatically" : true,
      	"numIndexesBefore" : 1,
      	"numIndexesAfter" : 2,
      	"ok" : 1,
              ...
      }
      rs0:PRIMARY> db.bar.getIndices()
      [
      	{
      		"v" : 2,
      		"key" : {
      			"_id" : 1
      		},
      		"name" : "_id_",
      		"ns" : "bar.foo"
      	},
      	{
      		"v" : 2,
      		"key" : {
      			"_id" : "hashed"
      		},
      		"name" : "_id_hashed",
      		"ns" : "bar.foo"
      	}
      ]
      rs0:PRIMARY> db.bar.find().min({_id: ""})
      { "_id" : "test" }
      1. Create empty replicaset member (node-2) and add to replicaset;
      2. Stepdown `node-1`;
      3. Check query (expected one record, but actually empty resultset):
      rs0:PRIMARY> db.bar.find().min({_id: ""})
      rs0:PRIMARY>  
      Show
      Install standalone replicaset (node-1); Create collection with hashed _id index: rs0:PRIMARY> use foo switched to db foo rs0:PRIMARY> db.bar.insert({_id: "test" }) WriteResult({ "nInserted" : 1 }) rs0:PRIMARY> db.bar.createIndex({_id: "hashed" }) { "createdCollectionAutomatically" : true , "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1, ... } rs0:PRIMARY> db.bar.getIndices() [ { "v" : 2, "key" : { "_id" : 1 }, "name" : "_id_" , "ns" : "bar.foo" }, { "v" : 2, "key" : { "_id" : "hashed" }, "name" : "_id_hashed" , "ns" : "bar.foo" } ] rs0:PRIMARY> db.bar.find().min({_id: ""}) { "_id" : "test" } Create empty replicaset member (node-2) and add to replicaset; Stepdown `node-1`; Check query (expected one record, but actually empty resultset): rs0:PRIMARY> db.bar.find().min({_id: ""}) rs0:PRIMARY>
    • Query 2019-04-22

      Issue

      Query with min/max modifier uses first index with related fields.

      On fresh MongoDB secondary startup `id` index became last in order (this looks like a bug) and `find` operation with `min`/`max` modifier starts using first hashed index by `_id`.

      When this secondary became primary, this shard returns incomplete results on query.

      user:PRIMARY> db.socialTopics.getIndices()
      [
      	{
      		"v" : 2,
      		"key" : {
      			"_id" : "hashed"
      		},
      		"name" : "_id_hashed",
      		"background" : true,
      		"ns" : "user.socialTopics"
      	},
      	{
      		"v" : 2,
      		"key" : {
      			"_id" : 1
      		},
      		"name" : "_id_",
      		"ns" : "user.socialTopics"
      	}
      ]
      user:PRIMARY> db.socialTopics.find({}, {_id: 1}).min({_id: ObjectId("5bb74d8cefa37173fe2cf692")}).sort({_id: 1}).limit(5).explain()
      {
      	"queryPlanner" : {
      		"plannerVersion" : 1,
      		"namespace" : "user.socialTopics",
      		"indexFilterSet" : false,
      		"parsedQuery" : {
      			
      		},
      		"winningPlan" : {
      			"stage" : "PROJECTION",
      			"transformBy" : {
      				"_id" : 1
      			},
      			"inputStage" : {
      				"stage" : "SORT",
      				"sortPattern" : {
      					"_id" : 1
      				},
      				"limitAmount" : 5,
      				"inputStage" : {
      					"stage" : "SORT_KEY_GENERATOR",
      					"inputStage" : {
      						"stage" : "FETCH",
      						"inputStage" : {
      							"stage" : "IXSCAN",
      							"keyPattern" : {
      								"_id" : "hashed"
      							},
      							"indexName" : "_id_hashed",
      							"isMultiKey" : false,
      							"isUnique" : false,
      							"isSparse" : false,
      							"isPartial" : false,
      							"indexVersion" : 2,
      							"direction" : "forward",
      							"indexBounds" : {
      								
      							}
      						}
      					}
      				}
      			}
      		},
      		"rejectedPlans" : [ ]
      	},
      	...
      } 

      Why we use min/max modifier

      We use min/max modifier for iterating over collections, because MongoDB don't have operation like `after` or `before`.

      We can't use `$gt`/`$gte`/`$lt`/`$lte` because:

      1. this operations always false for values with differ types;
      2. it's not trivial to make query with multiple field bounds.

      Workaround

      Looks like with `hint` we can set correct index directly.

            Assignee:
            ian.boros@mongodb.com Ian Boros
            Reporter:
            bozaro Artem
            Votes:
            2 Vote for this issue
            Watchers:
            12 Start watching this issue

              Created:
              Updated:
              Resolved: