MongoDB | 2597715

Info

A find query with a "skip" produces incorrect results when sorted on a missing/non-existent field. For example, on MongoDB 4.4+ both find and aggregate produce incorrect results: $ python repro2748.py MongoDB version: 4.4.19 Find docs with a single query: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19] Find docs with the same query with skip+limit: [0, 1, 1, 3, 3, 3, 3, 7, 7, 7, 7, 7, 7, 7, 7, 15, 15, 15, 15, 15] Find docs with the aggregation with skip+limit: [0, 1, 1, 3, 3, 3, 3, 7, 7, 7, 7, 7, 7, 7, 7, 15, 15, 15, 15, 15] On MongoDB $ python repro2748.py MongoDB version: 4.2.24 Find docs with a single query: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19] Find docs with the same query with skip+limit: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19] Find docs with the aggregation with skip+limit: [0, 1, 1, 3, 3, 3, 3, 7, 7, 7, 7, 7, 7, 7, 7, 15, 15, 15, 15, 15] Repro code: from pymongo import MongoClient client = MongoClient() coll = client.test.test version = client.server_info()['version'] print(f'MongoDB version: {version}') coll.drop() coll.insert_many([{"_id": i} for i in range(20)]) print('Find docs with a single query:') print([doc["_id"] for doc in coll.find(sort={'missing': 1})]) print('Find docs with the same query with skip+limit:') docs = [] for i in range(20): docs.append(coll.find_one(sort={'missing': 1}, skip=i)) print([doc["_id"] for doc in docs]) print('Find docs using aggregation with skip+limit:') docs = [] for i in range(20): docs.append(list(coll.aggregate([{"$sort": {'missing': 1}}, {"$skip": i}, {"$limit": 1}]))[0]) print([doc["_id"] for doc in docs]) Note this was originally reported via a MongoEngine issue here: https://github.com/MongoEngine/mongoengine/issues/2748

Top User Comments

JIRAUSER1270969 commented on Tue, 12 Mar 2024 13:22:02 +0000: As per documentation, there is no definite order that we store documents in, so the sort is inertially not stable. https://www.mongodb.com/docs/v4.4/reference/operator/aggregation/sort/#sort-consistency If consistent sort order is desired, include at least one field in your sort that contains unique values. The easiest way to guarantee this is to include the _id field in your sort query.

Steps to Reproduce