Details
-
Bug
-
Status: Closed
-
Major - P3
-
Resolution: Done
-
None
-
Fully Compatible
-
ALL
-
Query 2018-05-21, Query 2018-06-04, Query 2018-06-18, Query 2018-07-02
-
(copied to CRM)
-
0
Description
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.
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
- is related to
-
SERVER-18861 Queries matching null value should be fully covered by index
-
- Backlog
-