Sorting with equality does not ensure order in pagination

[arenier]

Prerequisites

• I have written a descriptive issue title
• I have searched existing issues to ensure the bug has not already been reported

Mongoose version

8.3.2

Node.js version

18.20.2

MongoDB server version

6.X

Typescript version (if applicable)

No response

Description

My CourseModel contains fields as title and code and other fields.

I have 3 courses with the same title "Communication" among tens of courses but different code "COM10", "COM11", "COM12".

I want to paginate the complete list by 10 and these 3 courses are between the 9th and 11th position. One is missing and one is in double between the two pages.

I remember I had the same issue with testing data I had to avoid same creation date to keep my unit tests clean. We updated mongoose from 7.5.0 to 8.0.0 in last november.

Steps to Reproduce

const courses = await CourseModel.find().sort('title').limit(10).skip(0);
console.log(courses.map(({ title, code }) => `${title} ${code}`));

/* [
  ... 
  ... 
  ... 
  ... 
  ... 
  ... 
  ... 
  ... 
  ... 
  'Communication COM10'
] */

const courses = await CourseModel.find().sort('title').limit(10).skip(10);
console.log(courses.map(({ title, code }) => `${title} ${code}`));

/*
[
  'Communication COM10',
  'Communication COM12',
  'Communication 2 COM20',
  ....
]
*/

Expected Behavior

const courses = await CourseModel.find().sort('title').limit(10).skip(0);
console.log(courses.map(({ title, code }) => `${title} ${code}`));

/* [
  ... 
  ... 
  ... 
  ... 
  ... 
  ... 
  ... 
  ... 
  ... 
  'Communication COM10'
] */

const courses = await CourseModel.find().sort('title').limit(10).skip(10);
console.log(courses.map(({ title, code }) => `${title} ${code}`));

/*
[
  'Communication COM11',
  'Communication COM12',
  'Communication 2 COM20',
  'XXXX',
  'XXXY',
   ....
]
*/

[IslandRhythms]

This may not necessarily be a bug. Its sorting documents with the same title and so order doesn't matter in that case which may cause the behavior being described.

const mongoose = require('mongoose');

const courseSchema = new mongoose.Schema({
    title: String,
    code: String
});


const Course = mongoose.model('Course', courseSchema);

async function run() {
    await mongoose.connect('mongodb://localhost:27017');
    await mongoose.connection.dropDatabase();

    for (let i = 0; i < 21; i++) {
        await Course.create({
            title: 'COM',
            code: `${i}`
        });
    }

    let courses = await Course.find().sort('title').limit(10).skip(0);
    console.log(courses.map(({ title, code }) => `${title} ${code}`));

    courses = await Course.find().sort('title').limit(10).skip(10);
    console.log(courses.map(({ title, code }) => `${title} ${code}`));

}

run();

[vkarpov15]

This is expected behavior on the MongoDB server. await CourseModel.find().sort('title') doesn't guarantee order beyond the title prop, so if you have multiple docs with the same title MongoDB may order them in any way in the result set.

There's a couple workarounds:

1. Sort by title and code: await CourseModel.find().sort({ title: 1, code: 1 }).limit(10).skip(10)
2. Sort by title and _id: await CourseModel.find().sort({ title: 1, _id: 1 }).limit(10).skip(10)

[arenier]

I was pretty sure, that was a MongoDB behavior.

That's strange to witness this behavior, I'll do the workaround. Thanks for help.