[DOCS-9994] Unique field must be included to ensure sort order stability across multiple queries Created: 10/Mar/17 Updated: 30/Oct/23 Due: 04/Dec/20 Resolved: 07/Dec/20 |
|
| Status: | Closed |
| Project: | Documentation |
| Component/s: | manual, Server |
| Affects Version/s: | None |
| Fix Version/s: | Server_Docs_20231030 |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | Kelsey Schubert | Assignee: | Andrew Feierabend (Inactive) |
| Resolution: | Done | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||||||||||
| Participants: | |||||||||||||||||||||||||
| Days since reply: | 3 years, 8 weeks, 2 days ago | ||||||||||||||||||||||||
| Epic Link: | DOCSP-11701 | ||||||||||||||||||||||||
| Story Points: | 4 | ||||||||||||||||||||||||
| Description |
|
There are number of reasons that the ordering of results with the same sort key might not be stable:
Therefore, we should clarify that users who require a stable sort order across queries (eg pagination) add a unique field to their sort. |
| Comments |
| Comment by Githook User [ 14/Dec/20 ] |
|
Author: {'name': 'Andrew Feierabend', 'email': 'andrew.feierabend@mongodb.com', 'username': 'andf-mongodb'}Message: DOCSP-13532 extend |
| Comment by Githook User [ 14/Dec/20 ] |
|
Author: {'name': 'Andrew Feierabend', 'email': 'andrew.feierabend@mongodb.com', 'username': 'andf-mongodb'}Message: DOCSP-13532 extend |
| Comment by Githook User [ 07/Dec/20 ] |
|
Author: {'name': 'Andrew Feierabend', 'email': 'andrew.feierabend@mongodb.com', 'username': 'andf-mongodb'}Message: |
| Comment by Githook User [ 07/Dec/20 ] |
|
Author: {'name': 'Andrew Feierabend', 'email': 'andrew.feierabend@mongodb.com', 'username': 'andf-mongodb'}Message: |
| Comment by David Storch [ 06/Nov/18 ] |
|
ravind.kumar, it looks like your questions have been answered, but happy to help if you need any more clarification. The bottom line is that any query which requests a sort in MQL (find command sort, the $sort stage, sort for findAndModify) does not guarantee stability within the set of documents that have the same sort key. Assuming you have (document, sort key) pairs (d1, 2), (d2, 2), (d3, 1), (d4, 8), it is guaranteed that the order for an ascending sort is either d3, d1, d2, d4 or d3, d2, d1, d4. Since d1 and d2 are equal, they may come back in either order. Applications that require an exact ordering must ensure that no two documents have the same sort key. |
| Comment by Kelsey Schubert [ 02/Nov/18 ] |
No
yes, there should be a compound index of the desired sort + one unique field at the end, and then the user should sort against this entire pattern. E.g. if they wanted to consistent sort on {a:1, b:1}, they could created an index and sort on {a:1, b:1, _id: 1}. |
| Comment by Chris Harris [ 02/Nov/18 ] |
|
Regarding the first question, a non-blocking sort uses an index. Per "Use Indexes to Sort Query Results":
It was also the topic of this Google Group discussion a few years ago. |
| Comment by Ravind Kumar (Inactive) [ 02/Nov/18 ] |
|
Taking this on from Andrew. A few questions:
What defines 'blocking sort operations' ? We document $sort for agg and cursor.sort() for queries - are those always blocking, or are there specific scenarios in which they block? cc david.storch
is this also true for WiredTiger? cc alexander.gorrod@mongodb.com
given that sorts can take advantage of indexes, is the implication here something like a compound unique index on the sort field + one additional field would be the ideal support for a consistently ordered sort? |
| Comment by Andrew Aldridge [ 24/Aug/18 ] |
|
I will take a look today! |