-
Type: Bug
-
Resolution: Done
-
Priority: Major - P3
-
Affects Version/s: None
-
Component/s: Querying
-
Fully Compatible
-
ALL
-
Query 2018-05-21, Query 2018-06-04, Query 2018-06-18, Query 2018-07-02
-
(copied to CRM)
-
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.
- is duplicated by
-
SERVER-44931 Covered queries do not work when $ne/$nin operators are present
- Closed
- is related to
-
SERVER-18861 Queries matching null value should be fully covered by index
- Backlog