[SERVER-53181] Lost results of search using sort, skip and limit Created: 02/Dec/20  Updated: 05/Dec/20  Resolved: 04/Dec/20

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: 4.4.2
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Евгений Махмудов Assignee: Edwin Zhou
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Docker


Attachments: File test_sort.js    
Issue Links:
Duplicate
duplicates SERVER-51498 Sorting on duplicate values causes re... Closed
Related
Operating System: ALL
Steps To Reproduce:

1. Use docker version of mongodb

docker run -d --name=test_mongo_44 mongo:4.4

2. Run attached file to populate collection and execute three find requests

cat test_sort.js | docker exec -i test_mongo_44 mongo

Participants:

 Description   

I found problem on production server and try to prepare minimal example that can reproduce that situation. I try to obtain in generally the same .explain() description of winningPlan, that's a reason why it possible looks ugly or strength. It just example.

The example use popular pattern of pagination of search request. First request try get first 5 documents, second request try next 5 documents, third request try get first 10 documents (total documents in this example)

Looks for output:

 

Query skip 0, limit 5:
{ "_id" : ObjectId("5fc7a98ff658d92832ebeb98") }
{ "_id" : ObjectId("5fc7a98ff658d92832ebeb99") }
{ "_id" : ObjectId("5fc7a98ff658d92832ebeb96") }
{ "_id" : ObjectId("5fc7a98ff658d92832ebeb95") }
{ "_id" : ObjectId("5fc7a98ff658d92832ebeb97") }
Query skip 5, limit 5:
{ "_id" : ObjectId("5fc7a98ff658d92832ebeb98") }
{ "_id" : ObjectId("5fc7a98ff658d92832ebeb96") }
{ "_id" : ObjectId("5fc7a98ff658d92832ebeb95") }
{ "_id" : ObjectId("5fc7a98ff658d92832ebeb97") }
{ "_id" : ObjectId("5fc7a98ff658d92832ebeb9b") }
Query skip 0, limit 10:
{ "_id" : ObjectId("5fc7a98ff658d92832ebeb9d") }
{ "_id" : ObjectId("5fc7a98ff658d92832ebeb9e") }
{ "_id" : ObjectId("5fc7a98ff658d92832ebeb99") }
{ "_id" : ObjectId("5fc7a98ff658d92832ebeb9c") }
{ "_id" : ObjectId("5fc7a98ff658d92832ebeb9a") }
{ "_id" : ObjectId("5fc7a98ff658d92832ebeb98") }
{ "_id" : ObjectId("5fc7a98ff658d92832ebeb96") }
{ "_id" : ObjectId("5fc7a98ff658d92832ebeb95") }
{ "_id" : ObjectId("5fc7a98ff658d92832ebeb97") }
{ "_id" : ObjectId("5fc7a98ff658d92832ebeb9b") }

As we  can see first and seconds results have intersections with ids:

5fc7a98ff658d92832ebeb98
5fc7a98ff658d92832ebeb96
5fc7a98ff658d92832ebeb95
5fc7a98ff658d92832ebeb97

and have no ids:

5fc7a98ff658d92832ebeb9d
5fc7a98ff658d92832ebeb9e
5fc7a98ff658d92832ebeb9c 
5fc7a98ff658d92832ebeb9a

which have third result.

 

In practice this mean that i lost objects when try to paginate request of web app.

 

I can't reproduce this behavior in versions 4.2, 4.0, 3.6, only in 4.4.2.

Is it a bug of mongodb?

P.S. My suggestion is that a new stage "SORT_KEY_GENERATOR" change behavior. It is a general difference between .explain() of 4.2 and 4.4 versions



 Comments   
Comment by Евгений Махмудов [ 05/Dec/20 ]

Hi asya . The situation which we talking about consider using all methods `.sort()` and `.skip()` and `.limit()`. Be sure that you properly read file `test_sort.js` in attachment.

Comment by Edwin Zhou [ 04/Dec/20 ]

Hi makhmudov.evgeniy@gmail.com,

I appreciate your input; we're currently considering ways to clarify the upgrade docs for 4.4 and we will be considering your feedback. This work is tracked in DOCS-9994 and has been reprioritized.

In the meantime, I will close this ticket as a duplicate of SERVER-51498, and you can refer to that ticket for updates on this issue.

Best,
Edwin

Comment by Asya Kamsky [ 04/Dec/20 ]

makhmudov.evgeniy@gmail.com if you do not specify $sort then the order of documents is NOT guaranteed - it was not guaranteed in 4.2 or earlier either, you were relying on the fact that it usually would stay the same (except in presence of writes, again, it could absolutely change).

 

Comment by Евгений Махмудов [ 04/Dec/20 ]

Hello @edwin.zhou 

Thank for reply!

It is a very sad situation, MongoDB version 4.4 breaks behavior that was the same in our project from MongoDB version 2.6.

In general, the idea that EVERY query which may use sort with skip and limit MUST use the additional unique sort parameter is horrible. I think between "two codepath for sort in query and aggregation" and "always determinate sort order in query" clients of DB prefer last.

I think anybody already asked the question - perhaps needs to add _id to sorting algorithm implicit? Because app developers will do this each time in client code.

Comment by Edwin Zhou [ 03/Dec/20 ]

Hi makhmudov.evgeniy@gmail.com,

Prior to MongoDB 4.4, query sort and aggregation sort used different codepaths. SERVER-28195 describes that aggregation sort does not use a stable sorting algorithm, so its behavior is expected. In MongoDB 4.4, we unified the codepath between query sort and aggregation sort, and those changes correspond to the change in query sorting behavior that you observed.

To ensure that the sort order is consistent across queries, we recommend including a unique field, such as _id in your sort order.

.sort({'_id':1, 'datetime': 1}) 

This suggestion is described by Mathias on SERVER-11407.

Best,
Edwin
 

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