-
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
-
-
- Closed
-
- is related to
-
SERVER-18861 Queries matching null value should be fully covered by index
-
- Backlog
-