I have MongoDB collection that have 5 documents, which I am running a query against it. The query fetches one document at a time.
the first 2 fetches return the expected result, while the last 3 fetches are fetching the third document only, while the last and second last documents are not being fetched at all.
Notice the last three documents have the same id.
db.A.find({}).limit(1).skip(0).sort({“startDate”: 1});
{ “_id” : ObjectId(“61097e72e0903145c925e445”)
db.A.find({}).limit(1).skip(1).sort({“startDate”: 1});
{ “_id” : ObjectId(“61098839ddd36878ba1acfef”)
db.A.find({}).limit(1).skip(2).sort({“startDate”: 1});
{ “_id” : ObjectId(“6112f90c5d7fc9e3ad1385ee”)
db.A.find({}).limit(1).skip(3).sort({“startDate”: 1});
{ “_id” : ObjectId(“6112f90c5d7fc9e3ad1385ee”)
db.A.find({}).limit(1).skip(4).sort({“startDate”: 1});
{ “_id” : ObjectId(“6112f90c5d7fc9e3ad1385ee”)
steevej
(Steeve Juneau)
August 12, 2021, 5:43pm
2
Could we have the full documents?
We are missing some information because there is no projection that only keep _id but we do not see the value of the field you sort on.
With full document is proper JSON format we could import in our env. and experiment.
Below is the entire collection
{ “_id” : ObjectId(“61097e72e0903145c925e445”), “startDate” : ISODate(“2021-06-14T15:47:19.551Z”), “endDate” : ISODate(“2021-09-14T15:47:19.551Z”), “lastModifiedDate” : ISODate(“2021-08-03T17:35:48.800Z”), “_class” : “entity.Offer” }
{ “_id” : ObjectId(“61098839ddd36878ba1acfef”), “startDate” : ISODate(“2021-06-14T15:47:19.551Z”), “endDate” : ISODate(“2021-09-14T15:47:19.551Z”), “lastModifiedDate” : ISODate(“2021-08-03T17:35:48.800Z”), “_class” : “entity.Offer” }
{ “_id” : ObjectId(“6112f90c5d7fc9e3ad1385ee”), “startDate” : ISODate(“2021-06-14T15:47:19.551Z”), “endDate” : ISODate(“2021-09-14T15:47:19.551Z”), “lastModifiedDate” : ISODate(“2021-08-03T17:35:48.800Z”), “_class” : “entity.Offer” }
{ “_id” : ObjectId(“611415503d6e6707ce6667c0”), “startDate” : ISODate(“2021-06-14T15:47:19.551Z”), “endDate” : ISODate(“2021-09-14T15:47:19.551Z”), “lastModifiedDate” : ISODate(“2021-08-03T17:35:48.800Z”), “_class” : “entity.Offer” }
{ “_id” : ObjectId(“61150b9c04457a8f8bcd44ab”), “startDate” : ISODate(“2021-06-14T15:47:19.551Z”), “endDate” : ISODate(“2021-09-14T15:47:19.551Z”), “lastModifiedDate” : ISODate(“2021-08-03T17:35:48.800Z”), “_class” : “entity.Offer” }
steevej
(Steeve Juneau)
August 12, 2021, 5:59pm
4
The quotes are messed up by HTML.
Please enclose withing 2 lines of triple back ticks such as
```
sorry for the hassle
[
{
"id": "61097e72e0903145c925e445",
"startDate": "2021-06-14T15:47:19.551Z",
"endDate": "2021-09-14T15:47:19.551Z"
},
{
"id": "61098839ddd36878ba1acfef",
"startDate": "2021-06-14T15:47:19.551Z",
"endDate": "2021-09-14T15:47:19.551Z"
},
{
"id": "6112f90c5d7fc9e3ad1385ee",
"startDate": "2021-06-14T15:47:19.551Z",
"endDate": "2021-09-14T15:47:19.551Z"
},
{
"id": "611415503d6e6707ce6667c0",
"startDate": "2021-06-14T15:47:19.551Z",
"endDate": "2021-09-14T15:47:19.551Z"
},
{
"id": "61150b9c04457a8f8bcd44ab",
"startDate": "2021-06-14T15:47:19.551Z",
"endDate": "2021-09-14T15:47:19.551Z"
}
]
looking at the start date, you will see it is identical.
I just changed the startDate now to be one second difference between each document. I can see now the queries are returning the expected result.
so below is my new JOSN
[
{
"id": "61097e72e0903145c925e445",
"startDate": "2021-06-14T15:47:19.551Z",
"endDate": "2021-09-14T15:47:19.551Z"
},
{
"id": "61098839ddd36878ba1acfef",
"startDate": "2021-06-14T15:47:20.551Z",
"endDate": "2021-09-14T15:47:19.551Z"
},
{
"id": "6112f90c5d7fc9e3ad1385ee",
"startDate": "2021-06-14T15:47:21.551Z",
"endDate": "2021-09-14T15:47:19.551Z"
},
{
"id": "611415503d6e6707ce6667c0",
"startDate": "2021-06-14T15:47:22.551Z",
"endDate": "2021-09-14T15:47:19.551Z"
},
{
"id": "61150b9c04457a8f8bcd44ab",
"startDate": "2021-06-14T15:47:23.551Z",
"endDate": "2021-09-14T15:47:19.551Z"
}
]
steevej
(Steeve Juneau)
August 13, 2021, 2:22am
7
I have tested mongo, mongosh, Compass, Atlas.
This is not the expected result.
I only get the correct result in Compass Aggregation.
To me it looks like a serious issue. As a temporary fix, you may sort { startDate:1 , _id:1 } and that seems to work.
I have flagged the thread so someone better suited will take a look at it.
Stennie_X
(Stennie)
August 13, 2021, 2:29am
8
Hi @Energy_N_A , @steevej ,
The issue is that is sorting by identical values does not produce a deterministic sort order:
Per Using skip()
with sort()
in the documentation:
If using skip()
with sort()
, be sure to include at least one field in your sort that contains unique values, before passing results to skip()
.
Sorting on fields that contain duplicate values may return an inconsistent sort order for those duplicate fields over multiple executions, especially when the collection is actively receiving writes.
The easiest way to guarantee sort consistency is to include the _id
field in your sort query.
Regards,
Stennie
3 Likes
steevej
(Steeve Juneau)
August 13, 2021, 2:37am
9
Thanks.
I did not look at the documentations in such a detailed way. So it is a well know issue, shame on me.
The intuition of adding _id:1 to the sort order was the good one. Yeah!
3 Likes
system
(system)
Closed
August 18, 2021, 7:10am
11
This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.