Info

In some cases, clustered collections incorrectly return results that should be filtered out by the $lt operator. Specifically, this query: db.clustered_repro.find({ _id: { $lt: 3 } }).sort({ _id: -1 }) May return a document with { _id: 3 }. The same happens when using an aggregation pipeline. Removing the sort condition, using ascending sort, or changing to $lte, resolves the issue. But I could find no workaround without changing the query or using a plain collection.

Top User Comments

Steps to Reproduce

NodeJS script reproducing the issue: import * as mongo from 'mongodb'; /** * Run with: * export MONGO_TEST_URL='mongodb://localhost:27017/clustered_test' * node script.mjs */ const MONGO_URL = process.env.MONGO_TEST_URL ?? 'mongodb://localhost:27017/clustered_test'; const client = new mongo.MongoClient(MONGO_URL); const db = client.db(); const clusteredName = `clustered_repro`; const plainName = `plain_repro`; await client.connect(); // Ensure the client is closed when the script finishes await using _ = { [Symbol.asyncDispose]: async () => await client.close() }; const buildInfo = await db.command({ buildInfo: 1 }); console.log(`MongoDB version: ${buildInfo.version}`); console.log(`Database: ${MONGO_URL}`); // Make sure the collections don't exist before running the test await db .collection(clusteredName) .drop() .catch(() => {}); await db .collection(plainName) .drop() .catch(() => {}); await db.createCollection(clusteredName, { clusteredIndex: { name: '_id', unique: true, key: { _id: 1 } } }); await db.createCollection(plainName); const clustered = db.collection(clusteredName); const plain = db.collection(plainName); const docs = [{ _id: 1 }, { _id: 2 }, { _id: 3 }, { _id: 4 }]; await clustered.insertMany(docs); await plain.insertMany(docs); const plainResults1 = await plain .find( { _id: { $lt: 3 } }, { sort: { _id: -1 } } ) .toArray(); const plainResults2 = await plain .aggregate([ { $match: { _id: { $lt: 3 } } }, { $sort: { _id: -1 } } ]) .toArray(); // This one fails - includes { _id: 3 } const clusteredResults1 = await clustered .find( { _id: { $lt: 3 } }, { sort: { _id: -1 } } ) .toArray(); // This one also fails - includes { _id: 3 } const clusteredResults2 = await clustered .aggregate([ { $match: { _id: { $lt: 3 } } }, { $sort: { _id: -1 } } ]) .toArray(); const clusteredResults3 = await clustered .aggregate([ { $match: { _id: { $lt: 3 } } }, { $sort: { _id: 1 } } ]) .toArray(); const clusteredResults4 = await clustered .find( { _id: { $lte: 2 } }, { sort: { _id: -1 } } ) .toArray(); const clusteredResults5 = await clustered .aggregate([ { $match: { _id: { $lte: 2 } } }, { $sort: { _id: -1 } } ]) .toArray(); console.log('These should all filter out { _id: 3 }'); console.log('Plain 1:', plainResults1, '# find'); console.log('Plain 2:', plainResults2, '# aggregate'); console.log('Clustered 1:', clusteredResults1, '# find'); console.log('Clustered 2:', clusteredResults2, '# aggregate, sort _id: -1'); console.log('Clustered 3:', clusteredResults3, '# aggregate, sort _id: 1'); console.log('Clustered 4:', clusteredResults4, '# find, sort _id: -1, $lte 2'); console.log('Clustered 5:', clusteredResults5, '# aggregate, sort _id: -1, $lte 2'); Tested on 8.0.19 and 8.2.6-rc0. Results: MongoDB version: 8.2.6-rc0 Database: mongodb://localhost:27017/clustered_test These should all filter out { _id: 3 } Plain 1: [ { _id: 2 }, { _id: 1 } ] # find Plain 2: [ { _id: 2 }, { _id: 1 } ] # aggregate Clustered 1: [ { _id: 3 }, { _id: 2 }, { _id: 1 } ] # find Clustered 2: [ { _id: 3 }, { _id: 2 }, { _id: 1 } ] # aggregate, sort _id: -1 Clustered 3: [ { _id: 1 }, { _id: 2 } ] # aggregate, sort _id: 1 Clustered 4: [ { _id: 2 }, { _id: 1 } ] # find, sort _id: -1, $lte 2 Clustered 5: [ { _id: 2 }, { _id: 1 } ] # aggregate, sort _id: -1, $lte 2