[SERVER-43105] Sort operation retun duplicate element Created: 30/Aug/19  Updated: 03/Sep/19  Resolved: 03/Sep/19

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: 3.6.0, 4.2.0
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Alexander Makedonski Assignee: Carl Champain (Inactive)
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-28195 $skip followed by $limit in aggregati... Closed
Operating System: ALL
Steps To Reproduce:

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

.

 

Participants:

 Description   

Tested on Mongodb v4.2 and  Mongodb 3.6

OS: Ubuntu 18.04

Using aggregation with lookup sort, limit and skip returned value is duplicated.



 Comments   
Comment by Carl Champain (Inactive) [ 03/Sep/19 ]

Hi Alexander,

Thanks for reporting this behavior!
This is actually a duplicate of SERVER-28195, please take a close look at this comment which provides a workaround.

Best,
Carl

Generated at Thu Feb 08 05:02:17 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.