-
Type:
Bug
-
Resolution: Duplicate
-
Priority:
Major - P3
-
None
-
Affects Version/s: 3.6.0-rc1
-
Component/s: Querying
-
Labels:None
-
Query
-
ALL
When dotted field plan is generated for distinct command, it uses DISTINCT_SCAN without FETCH.
db.orders.find()
{ "_id" : ObjectId("5a006024e946f6d003f4cf93"), "lineitems" : { "returnflag" : "A" } }
{ "_id" : ObjectId("5a006028e946f6d003f4cf94"), "lineitems" : { "returnflag" : "B" } }
{ "_id" : ObjectId("5a006383e946f6d003f4cf96"), "lineitems" : { "otherflag" : 1 } }
/* without indexes */
db.orders.distinct("lineitems.returnflag")
[ "A", "B" ]
db.orders.createIndex({"lineitems.returnflag":1, "lineitems.linestatus" : 1})
/* ok */
db.orders.distinct("lineitems.returnflag")
[ null, "A", "B" ]
/* make index multikey */
db.orders.insert({lineitems:[ {returnflag:"A"}, {returnflag:"B"} ]})
WriteResult({ "nInserted" : 1 })
db.orders.distinct("lineitems.returnflag")
[ "A", "B" ]
Note in case of using non-multikey index we show null (for missing).
On nonMultikey index explain was:
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"_id" : 0,
"lineitems.returnflag" : 1
},
"inputStage" : {
"stage" : "DISTINCT_SCAN",
"keyPattern" : {
"lineitems.returnflag" : 1,
"lineitems.linestatus" : 1
},
"indexName" : "lineitems.returnflag_1_lineitems.linestatus_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"lineitems.returnflag" : [ ],
"lineitems.linestatus" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"lineitems.returnflag" : [
"[MinKey, MaxKey]"
],
"lineitems.linestatus" : [
"[MinKey, MaxKey]"
]
}
}
},
hence picking up the null index value (which represented missing).
After the insert, it's:
"winningPlan" : {
"stage" : "COLLSCAN",
"direction" : "forward"
},
This change seems due to SERVER-2104 or SERVER-3173?
It's a correctness regression. Here's plan from 3.4.6 which correctly does not return NULL:
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"_id" : 0,
"lineitems.status" : 1
},
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "DISTINCT_SCAN",
"keyPattern" : {
"lineitems.status" : 1,
"lineitems.date" : 1
},
"indexName" : "lineitems.status_1_lineitems.date_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"lineitems.status" : [ ],
"lineitems.date" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"lineitems.status" : [
"[MinKey, MaxKey]"
],
"lineitems.date" : [
"[MinKey, MaxKey]"
]
}
}
}
},
3.4 correctly returns no "null" with or without the index.
- duplicates
-
-
- Needs Scheduling
-