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

covered index should be used when null inequality is in the query and index is not multikey

    • Fully Compatible
    • ALL
    • Query 2018-05-21, Query 2018-06-04, Query 2018-06-18, Query 2018-07-02
    • 0

      Having index on

      {a:1, b:1}

      here are some plans:
      Good:

      > db.ix.explain().find({a:1,b:2},{_id:0,a:1})
      {
      	"queryPlanner" : {
      		"plannerVersion" : 1,
      		"namespace" : "test.ix",
      		"indexFilterSet" : false,
      		"parsedQuery" : {
      			"$and" : [
      				{
      					"a" : {
      						"$eq" : 1
      					}
      				},
      				{
      					"b" : {
      						"$eq" : 2
      					}
      				}
      			]
      		},
      		"winningPlan" : {
      			"stage" : "PROJECTION",
      			"transformBy" : {
      				"_id" : 0,
      				"a" : 1
      			},
      			"inputStage" : {
      				"stage" : "IXSCAN",
      				"keyPattern" : {
      					"a" : 1,
      					"b" : 1
      				},
      				"indexName" : "a_1_b_1",
      				"isMultiKey" : false,
      				"multiKeyPaths" : {
      					"a" : [ ],
      					"b" : [ ]
      				},
      				"isUnique" : false,
      				"isSparse" : false,
      				"isPartial" : false,
      				"indexVersion" : 2,
      				"direction" : "forward",
      				"indexBounds" : {
      					"a" : [
      						"[1.0, 1.0]"
      					],
      					"b" : [
      						"[2.0, 2.0]"
      					]
      				}
      			}
      		},
      		"rejectedPlans" : [ ]
      	},
      	"serverInfo" : {
      		"host" : "altoros4",
      		"port" : 34001,
      		"version" : "3.5.1-89-g7b68733",
      		"gitVersion" : "7b68733fae7ce8d91f9a5fda67b9972c15a20ef0"
      	},
      	"ok" : 1
      }
      

      Good:

      > db.ix.explain().find({a:1,b:{$ne:2}},{_id:0,a:1})
      {
      	"queryPlanner" : {
      		"plannerVersion" : 1,
      		"namespace" : "test.ix",
      		"indexFilterSet" : false,
      		"parsedQuery" : {
      			"$and" : [
      				{
      					"a" : {
      						"$eq" : 1
      					}
      				},
      				{
      					"$nor" : [
      						{
      							"b" : {
      								"$eq" : 2
      							}
      						}
      					]
      				}
      			]
      		},
      		"winningPlan" : {
      			"stage" : "PROJECTION",
      			"transformBy" : {
      				"_id" : 0,
      				"a" : 1
      			},
      			"inputStage" : {
      				"stage" : "IXSCAN",
      				"keyPattern" : {
      					"a" : 1,
      					"b" : 1
      				},
      				"indexName" : "a_1_b_1",
      				"isMultiKey" : false,
      				"multiKeyPaths" : {
      					"a" : [ ],
      					"b" : [ ]
      				},
      				"isUnique" : false,
      				"isSparse" : false,
      				"isPartial" : false,
      				"indexVersion" : 2,
      				"direction" : "forward",
      				"indexBounds" : {
      					"a" : [
      						"[1.0, 1.0]"
      					],
      					"b" : [
      						"[MinKey, 2.0)",
      						"(2.0, MaxKey]"
      					]
      				}
      			}
      		},
      		"rejectedPlans" : [ ]
      	},
      	"serverInfo" : {
      		"host" : "altoros4",
      		"port" : 34001,
      		"version" : "3.5.1-89-g7b68733",
      		"gitVersion" : "7b68733fae7ce8d91f9a5fda67b9972c15a20ef0"
      	},
      	"ok" : 1
      }
      

      Bad:

      > db.ix.explain().find({a:1,b:{$ne:null}},{_id:0,a:1})
      {
      	"queryPlanner" : {
      		"plannerVersion" : 1,
      		"namespace" : "test.ix",
      		"indexFilterSet" : false,
      		"parsedQuery" : {
      			"$and" : [
      				{
      					"a" : {
      						"$eq" : 1
      					}
      				},
      				{
      					"$nor" : [
      						{
      							"b" : {
      								"$eq" : null
      							}
      						}
      					]
      				}
      			]
      		},
      		"winningPlan" : {
      			"stage" : "PROJECTION",
      			"transformBy" : {
      				"_id" : 0,
      				"a" : 1
      			},
      			"inputStage" : {
      				"stage" : "FETCH",
      				"filter" : {
      					"$nor" : [
      						{
      							"b" : {
      								"$eq" : null
      							}
      						}
      					]
      				},
      				"inputStage" : {
      					"stage" : "IXSCAN",
      					"keyPattern" : {
      						"a" : 1,
      						"b" : 1
      					},
      					"indexName" : "a_1_b_1",
      					"isMultiKey" : false,
      					"multiKeyPaths" : {
      						"a" : [ ],
      						"b" : [ ]
      					},
      					"isUnique" : false,
      					"isSparse" : false,
      					"isPartial" : false,
      					"indexVersion" : 2,
      					"direction" : "forward",
      					"indexBounds" : {
      						"a" : [
      							"[1.0, 1.0]"
      						],
      						"b" : [
      							"[MinKey, null)",
      							"(null, MaxKey]"
      						]
      					}
      				}
      			}
      		},
      		"rejectedPlans" : [ ]
      	},
      	"serverInfo" : {
      		"host" : "altoros4",
      		"port" : 34001,
      		"version" : "3.5.1-89-g7b68733",
      		"gitVersion" : "7b68733fae7ce8d91f9a5fda67b9972c15a20ef0"
      	},
      	"ok" : 1
      }
      

      Why is there a FETCH to compare b!=null? Seems like a bug.

            Assignee:
            charlie.swanson@mongodb.com Charlie Swanson
            Reporter:
            asya.kamsky@mongodb.com Asya Kamsky
            Votes:
            5 Vote for this issue
            Watchers:
            15 Start watching this issue

              Created:
              Updated:
              Resolved: