Show
Insert in database these collections:
db.products.insert([
{ "_id" : ObjectId( "5d605c5562348f7a74c3e86b" ), "name" : "My name 1" , "price" : 15, "key" : 3, "createdAt" : ISODate( "2019-08-23T21:36:21.492Z" ), "updatedAt" : ISODate( "2019-08-30T16:55:15.536Z" )},
{ "_id" : ObjectId( "5d63b34f6c07b33ac413014a" ), "name" : "My name 2" , "price" : 14.90, "key" : 4, "createdAt" : ISODate( "2019-08-26T10:24:15.701Z" ), "updatedAt" : ISODate( "2019-08-30T16:56:20.224Z" )},
{ "_id" : ObjectId( "5d63b3aa6c07b33ac413014c" ), "name" : "My name 3" , "price" : 14.90, "key" : 5, "createdAt" : ISODate( "2019-08-26T10:25:46.238Z" ), "updatedAt" : ISODate( "2019-08-30T16:56:25.671Z" )},
{ "_id" : ObjectId( "5d6778d7a053172602d33b44" ), "name" : "My name 4" , "price" : 17, "key" : 6, "createdAt" : ISODate( "2019-08-29T07:03:51.332Z" ), "updatedAt" : ISODate( "2019-08-29T07:03:51.332Z" )}
])
db.productCharacteristics.insert([
{ "_id" : ObjectId( "5d60624e1e09e27e89986562" ), "active" : true , "productKey" : 3, "priority" : 0, "createdAt" : ISODate( "2019-08-23T22:01:50.681Z" ), "updatedAt" : ISODate( "2019-08-30T16:55:15.537Z" )},
{ "_id" : ObjectId( "5d63b34f6c07b33ac413014b" ), "active" : true , "productKey" : 4, "priority" : 0, "createdAt" : ISODate( "2019-08-26T10:24:15.706Z" ), "updatedAt" : ISODate( "2019-08-30T16:56:20.224Z" )},
{ "_id" : ObjectId( "5d63b3aa6c07b33ac413014d" ), "active" : true , "productKey" : 5, "priority" : 0, "createdAt" : ISODate( "2019-08-26T10:25:46.239Z" ), "updatedAt" : ISODate( "2019-08-30T16:56:25.672Z" )},
{ "_id" : ObjectId( "5d6778d7a053172602d33b45" ), "active" : true , "productKey" : 6, "priority" : 0, "createdAt" : ISODate( "2019-08-29T07:03:51.339Z" ), "updatedAt" : ISODate( "2019-08-29T07:03:51.339Z" )}
])
Then using this query
db.productCharacteristics.aggregate([
{ '$lookup' : { from: 'products' , localField: 'productKey' , foreignField: 'key' , as: 'data' } },
{ '$unwind' : { path: '$data' , preserveNullAndEmptyArrays: true } },
{ '$match' : { 'data.price' : { $gte: 12, $lte: 17 } } },
{ '$sort' : { 'data.price' : 1 } },
{ '$skip' : 0 },
{ '$limit' : 1 }
]).pretty()
Result is as expected
{{ "_id" : ObjectId( "5d63b34f6c07b33ac413014b" ), "active" : true , "productKey" : 4, "priority" : 0, "createdAt" : ISODate( "2019-08-26T10:24:15.706Z" ), "updatedAt" : ISODate( "2019-08-30T16:56:20.224Z" ), "data" : { "_id" : ObjectId( "5d63b34f6c07b33ac413014a" ), "name" : "My name 2" , "price" : 14.9, "key" : 4, "createdAt" : ISODate( "2019-08-26T10:24:15.701Z" ), "updatedAt" : ISODate( "2019-08-30T16:56:20.224Z" ) }}
then lets change '$skip': 0 to '$skip': 1 in the same query as above, then we got the same result. Which is totaly unexpected .
If we remove $skip and $limit, like this
db.productCharacteristics.aggregate([
{ '$lookup' : { from: 'products' , localField: 'productKey' , foreignField: 'key' , as: 'data' } },
{ '$unwind' : { path: '$data' , preserveNullAndEmptyArrays: true } },
{ '$match' : { 'data.price' : { $gte: 12, $lte: 17 } } },
{ '$sort' : { 'data.price' : 1 } }
]).pretty()
result is what we expect no duplicate items no unexpected behavior. The real problem is why when I skip the element I got the previous element
.
Bug
Major - P3