Details
-
Bug
-
Status: Closed
-
Major - P3
-
Resolution: Fixed
-
3.4.5, 3.5.13
-
Fully Compatible
-
ALL
-
v4.0, v3.6, v3.4
-
Query 2018-11-19, Query 2018-12-03
-
(copied to CRM)
Description
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.
Attachments
Issue Links
- related to
-
-
- Closed
-