Single fetch with skip and limit returns unexpected result

[Energy_N_A]

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)]

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.

[Energy_N_A]

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)]

The quotes are messed up by HTML.

Please enclose withing 2 lines of triple back ticks such as

```

[Energy_N_A]

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"
    }
]

[Energy_N_A]

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)]

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]

Hi @Energy_N_A, @steevej,

The issue is that is sorting by identical values does not produce a deterministic sort order:

@Energy_N_A

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.

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

[steevej (Steeve Juneau)]

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!

[Energy_N_A]

Thanks a lot Steve!

Appreciate your help!