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

Server does not appropriately respect setting internalQueryEnumerationMaxOrSolutions to 0

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor - P4
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 4.9.0
    • Component/s: None
    • Labels:
    • Backwards Compatibility:
      Fully Compatible
    • Operating System:
      ALL
    • Steps To Reproduce:
      Hide

      db.foo.drop()
      db.foo.createIndex({common:1})
      db.foo.createIndex({one:1})
      db.foo.createIndex({two:1})
       
      // Confirming that with the default setting of 10, a total of 5 plans (4 rejectedPlans) are generated: 
      db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:10})
      db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
       
       
      // Setting this to 0 should result in only a single plan, using the { common: 1 } index, being generated.  However there is a rejected "OR" plan:
      db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:0})
      db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
      db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans[0]
       
       
      // With a value of 1, we get the same result as when it is 0.  This is output seems correct for this setting:
      db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:1})
      db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
      db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans[0]
       
      // Further increases to the parameter have the expected increase to the number of plans:
      db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:2})
      db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
      db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:3})
      db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
      db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:4})
      db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length

      Full output from a mongo shell session against a 4.2.2 mongod:

      > db.foo.drop()
      true
      > 
      > db.foo.createIndex({common:1})
      {
      	"createdCollectionAutomatically" : true,
      	"numIndexesBefore" : 1,
      	"numIndexesAfter" : 2,
      	"ok" : 1
      }
      > db.foo.createIndex({one:1})
      {
      	"createdCollectionAutomatically" : false,
      	"numIndexesBefore" : 2,
      	"numIndexesAfter" : 3,
      	"ok" : 1
      }
      > db.foo.createIndex({two:1})
      {
      	"createdCollectionAutomatically" : false,
      	"numIndexesBefore" : 3,
      	"numIndexesAfter" : 4,
      	"ok" : 1
      }
      > 
      > 
      > // Confirming that with the default setting of 10, a total of 5 plans (4 rejectedPlans) are generated:
      > db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:10})
      { "was" : 10, "ok" : 1 }
      > db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
      4
      > 
      > 
      > // Setting this to 0 should result in only a single plan, using the { common: 1 } index, being generated.  However there is a rejected "OR" plan:
      > db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:0})
      { "was" : 10, "ok" : 1 }
      > db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
      1
      > db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans[0]
      {
      	"stage" : "FETCH",
      	"filter" : {
      		"common" : {
      			"$eq" : 1
      		}
      	},
      	"inputStage" : {
      		"stage" : "OR",
      		"inputStages" : [
      			{
      				"stage" : "FETCH",
      				"filter" : {
      					"two" : {
      						"$eq" : 0
      					}
      				},
      				"inputStage" : {
      					"stage" : "IXSCAN",
      					"keyPattern" : {
      						"one" : 1
      					},
      					"indexName" : "one_1",
      					"isMultiKey" : false,
      					"multiKeyPaths" : {
      						"one" : [ ]
      					},
      					"isUnique" : false,
      					"isSparse" : false,
      					"isPartial" : false,
      					"indexVersion" : 2,
      					"direction" : "forward",
      					"indexBounds" : {
      						"one" : [
      							"[0.0, 0.0]"
      						]
      					}
      				}
      			},
      			{
      				"stage" : "FETCH",
      				"filter" : {
      					"one" : {
      						"$eq" : 1
      					}
      				},
      				"inputStage" : {
      					"stage" : "IXSCAN",
      					"keyPattern" : {
      						"two" : 1
      					},
      					"indexName" : "two_1",
      					"isMultiKey" : false,
      					"multiKeyPaths" : {
      						"two" : [ ]
      					},
      					"isUnique" : false,
      					"isSparse" : false,
      					"isPartial" : false,
      					"indexVersion" : 2,
      					"direction" : "forward",
      					"indexBounds" : {
      						"two" : [
      							"[1.0, 1.0]"
      						]
      					}
      				}
      			}
      		]
      	}
      }
      > 
      > // With a value of 1, we get the same result as when it is 0.  But the output now seems correct based on the updated parameter value:
      > db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:1})
      { "was" : 0, "ok" : 1 }
      > db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
      1
      > db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans[0]
      {
      	"stage" : "FETCH",
      	"filter" : {
      		"common" : {
      			"$eq" : 1
      		}
      	},
      	"inputStage" : {
      		"stage" : "OR",
      		"inputStages" : [
      			{
      				"stage" : "FETCH",
      				"filter" : {
      					"two" : {
      						"$eq" : 1
      					}
      				},
      				"inputStage" : {
      					"stage" : "IXSCAN",
      					"keyPattern" : {
      						"one" : 1
      					},
      					"indexName" : "one_1",
      					"isMultiKey" : false,
      					"multiKeyPaths" : {
      						"one" : [ ]
      					},
      					"isUnique" : false,
      					"isSparse" : false,
      					"isPartial" : false,
      					"indexVersion" : 2,
      					"direction" : "forward",
      					"indexBounds" : {
      						"one" : [
      							"[1.0, 1.0]"
      						]
      					}
      				}
      			},
      			{
      				"stage" : "FETCH",
      				"filter" : {
      					"one" : {
      						"$eq" : 0
      					}
      				},
      				"inputStage" : {
      					"stage" : "IXSCAN",
      					"keyPattern" : {
      						"two" : 1
      					},
      					"indexName" : "two_1",
      					"isMultiKey" : false,
      					"multiKeyPaths" : {
      						"two" : [ ]
      					},
      					"isUnique" : false,
      					"isSparse" : false,
      					"isPartial" : false,
      					"indexVersion" : 2,
      					"direction" : "forward",
      					"indexBounds" : {
      						"two" : [
      							"[0.0, 0.0]"
      						]
      					}
      				}
      			}
      		]
      	}
      }
      > 
      > 
      > // Further increases to the parameter have the expected corresponding increase to the number of plans:
      > db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:2})
      { "was" : 1, "ok" : 1 }
      > db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
      2
      > db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:3})
      { "was" : 2, "ok" : 1 }
      > db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
      3
      > db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:4})
      { "was" : 3, "ok" : 1 }
      > db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length
      4 

      Show
      db.foo.drop() db.foo.createIndex({common:1}) db.foo.createIndex({one:1}) db.foo.createIndex({two:1})   // Confirming that with the default setting of 10, a total of 5 plans (4 rejectedPlans) are generated: db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:10}) db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length     // Setting this to 0 should result in only a single plan, using the { common: 1 } index, being generated.  However there is a rejected "OR" plan: db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:0}) db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans[0]     // With a value of 1, we get the same result as when it is 0.  This is output seems correct for this setting: db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:1}) db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans[0]   // Further increases to the parameter have the expected increase to the number of plans: db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:2}) db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:3}) db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:4}) db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length Full output from a mongo shell session against a 4.2.2 mongod : > db.foo.drop() true > > db.foo.createIndex({common:1}) { "createdCollectionAutomatically" : true, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1 } > db.foo.createIndex({one:1}) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 2, "numIndexesAfter" : 3, "ok" : 1 } > db.foo.createIndex({two:1}) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 3, "numIndexesAfter" : 4, "ok" : 1 } > > > // Confirming that with the default setting of 10, a total of 5 plans (4 rejectedPlans) are generated: > db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:10}) { "was" : 10, "ok" : 1 } > db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length 4 > > > // Setting this to 0 should result in only a single plan, using the { common: 1 } index, being generated.  However there is a rejected "OR" plan: > db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:0}) { "was" : 10, "ok" : 1 } > db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length 1 > db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans[0] { "stage" : "FETCH", "filter" : { "common" : { "$eq" : 1 } }, "inputStage" : { "stage" : "OR", "inputStages" : [ { "stage" : "FETCH", "filter" : { "two" : { "$eq" : 0 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "one" : 1 }, "indexName" : "one_1", "isMultiKey" : false, "multiKeyPaths" : { "one" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "one" : [ "[0.0, 0.0]" ] } } }, { "stage" : "FETCH", "filter" : { "one" : { "$eq" : 1 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "two" : 1 }, "indexName" : "two_1", "isMultiKey" : false, "multiKeyPaths" : { "two" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "two" : [ "[1.0, 1.0]" ] } } } ] } } > > // With a value of 1, we get the same result as when it is 0.  But the output now seems correct based on the updated parameter value: > db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:1}) { "was" : 0, "ok" : 1 } > db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length 1 > db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans[0] { "stage" : "FETCH", "filter" : { "common" : { "$eq" : 1 } }, "inputStage" : { "stage" : "OR", "inputStages" : [ { "stage" : "FETCH", "filter" : { "two" : { "$eq" : 1 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "one" : 1 }, "indexName" : "one_1", "isMultiKey" : false, "multiKeyPaths" : { "one" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "one" : [ "[1.0, 1.0]" ] } } }, { "stage" : "FETCH", "filter" : { "one" : { "$eq" : 0 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "two" : 1 }, "indexName" : "two_1", "isMultiKey" : false, "multiKeyPaths" : { "two" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "two" : [ "[0.0, 0.0]" ] } } } ] } } > > > // Further increases to the parameter have the expected corresponding increase to the number of plans: > db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:2}) { "was" : 1, "ok" : 1 } > db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length 2 > db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:3}) { "was" : 2, "ok" : 1 } > db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length 3 > db.adminCommand({setParameter:1, internalQueryEnumerationMaxOrSolutions:4}) { "was" : 3, "ok" : 1 } > db.foo.find({common:1, $or:[{one:0, two:0},{one:1, two:1}]}).explain().queryPlanner.rejectedPlans.length 4
    • Sprint:
      Query 2020-06-01, Query 2020-06-15, Query 2020-06-29, Query 2020-07-13, Query 2020-07-27, Query 2020-08-10, Query 2020-08-24, Query 2020-09-07, Query 2020-09-21, Query 2020-10-05, Query 2020-10-19

      Description

      When internalQueryEnumerationMaxOrSolutions is set to 0 the database will still generate and consider a plan which contains a "OR" stage.  This is the same behavior as when the parameter is set to a value of {{1 }}so it seems incorrect.  Further increases to the parameter after that generate additional plans as expected.  

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              ted.tuckman Ted Tuckman
              Reporter:
              christopher.harris Christopher Harris
              Participants:
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: