-
Type:
Bug
-
Resolution: Fixed
-
Priority:
Major - P3
-
Affects Version/s: 3.4.5, 3.5.13
-
Component/s: Aggregation Framework, Diagnostics
-
Fully Compatible
-
ALL
-
v4.0, v3.6, v3.4
-
Query 2018-11-19, Query 2018-12-03
-
(copied to CRM)
Using the tpch denormalized dataset: http://noexpire.s3.amazonaws.com/sqlproxy/data/tpch_full_denormalized.bson.gz
I run the following aggregation pipeline:
db.setProfilingLevel(2)
db.mongo_part.aggregate(
[
{
"$match" : {
"p_suppliers" : {
"$elemMatch" : {
"ps_s_r_name" : "EUROPE"
}
}
}
},
{
"$unwind" : {
"includeArrayIndex" : "p_suppliers_idx",
"path" : "$p_suppliers"
}
},
{
"$match" : {
"p_suppliers.ps_s_r_name" : "EUROPE"
}
},
{
"$match" : {
"$and" : [
{
"p_size" : NumberLong(15)
},
{
"p_type" : {
"$regex" : /^.*BRASS$/i
}
}
]
}
},
{
"$group" : {
"min(mongo_partsupp_DOT_ps_supplycost)" : {
"$min" : "$p_suppliers.ps_supplycost"
},
"_id" : {
"mongo_part_DOT_p_partkey" : "$_id"
}
}
},
{
"$project" : {
"mongo_part_DOT_p_partkey" : "$_id.mongo_part_DOT_p_partkey",
"min(mongo_partsupp_DOT_ps_supplycost)" : "$min(mongo_partsupp_DOT_ps_supplycost)"
}
},
{
"$match" : {
"mongo_part_DOT_p_partkey" : {
"$ne" : null
}
}
},
{
"$lookup" : {
"as" : "__joined_mongo_part",
"from" : "mongo_part",
"localField" : "mongo_part_DOT_p_partkey",
"foreignField" : "_id"
}
},
{
"$unwind" : {
"path" : "$__joined_mongo_part",
"preserveNullAndEmptyArrays" : false
}
},
{
"$match" : {
"__joined_mongo_part._id" : {
"$ne" : null
}
}
},
{
"$lookup" : {
"from" : "mongo_part",
"localField" : "__joined_mongo_part._id",
"foreignField" : "_id",
"as" : "__joined_mongo_partsupp"
}
},
{
"$unwind" : {
"path" : "$__joined_mongo_partsupp",
"preserveNullAndEmptyArrays" : false
}
},
{
"$unwind" : {
"path" : "$__joined_mongo_partsupp.p_suppliers",
"includeArrayIndex" : "__joined_mongo_partsupp.p_suppliers_idx",
"preserveNullAndEmptyArrays" : false
}
},
{
"$addFields" : {
"__predicate" : {
"$let" : {
"vars" : {
"predicate" : {
"$cond" : [
{
"$or" : [
{
"$eq" : [
{
"$ifNull" : [
"$__joined_mongo_partsupp.p_suppliers.ps_supplycost",
null
]
},
null
]
},
{
"$eq" : [
{
"$ifNull" : [
"$min(mongo_partsupp_DOT_ps_supplycost)",
null
]
},
null
]
}
]
},
null,
{
"$eq" : [
"$__joined_mongo_partsupp.p_suppliers.ps_supplycost",
"$min(mongo_partsupp_DOT_ps_supplycost)"
]
}
]
}
},
"in" : {
"$cond" : [
{
"$or" : [
{
"$eq" : [
"$$predicate",
false
]
},
{
"$eq" : [
"$$predicate",
0
]
},
{
"$eq" : [
"$$predicate",
"0"
]
},
{
"$eq" : [
"$$predicate",
"-0"
]
},
{
"$eq" : [
"$$predicate",
"0.0"
]
},
{
"$eq" : [
"$$predicate",
"-0.0"
]
},
{
"$eq" : [
"$$predicate",
null
]
}
]
},
false,
true
]
}
}
}
}
},
{
"$match" : {
"__predicate" : true
}
},
{
"$match" : {
"__joined_mongo_partsupp.p_suppliers.ps_suppkey" : {
"$ne" : null
}
}
},
{
"$lookup" : {
"from" : "mongo_supplier",
"localField" : "__joined_mongo_partsupp.p_suppliers.ps_suppkey",
"foreignField" : "_id",
"as" : "__joined_mongo_supplier"
}
},
{
"$unwind" : {
"path" : "$__joined_mongo_supplier",
"preserveNullAndEmptyArrays" : false
}
},
{
"$project" : {
"mongo_partsupp_DOT_ps_suppkey" : "$__joined_mongo_partsupp.p_suppliers.ps_suppkey",
"mongo_supplier_DOT_s_comment" : "$__joined_mongo_supplier.s_comment",
"mongo_supplier_DOT_s_n_name" : "$__joined_mongo_supplier.s_n_name",
"mongo_part_DOT_p_mfgr" : "$__joined_mongo_part.p_mfgr",
"mongo_part_DOT_p_partkey" : "$__joined_mongo_part._id",
"mongo_supplier_DOT_s_address" : "$__joined_mongo_supplier.s_address",
"mongo_supplier_DOT_s_name" : "$__joined_mongo_supplier.s_name",
"mongo_supplier_DOT_s_phone" : "$__joined_mongo_supplier.s_phone",
"mongo_supplier_DOT_s_acctbal" : "$__joined_mongo_supplier.s_acctbal"
}
}
]
)
The entry in system.profile now contains the wrong ns:
db.system.profile.findOne().ns tpch.mongo_supplier
Note that if the same aggregation is executed against an empty database, it actually does result in the right ns (emptydb.mongo_part) logged in system.profile.
- related to
-
-
- Closed
-