Clustered collections do not correctly filter out results with $lt on _id

XMLWordPrintableJSON

    • Query Optimization
    • Fully Compatible
    • ALL
    • v8.3, v8.2, v8.0
    • Hide

      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

       

      Show
      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  
    • 200
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      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.

            Assignee:
            Naafiyan Ahmed
            Reporter:
            Ralf Kistner
            Votes:
            0 Vote for this issue
            Watchers:
            11 Start watching this issue

              Created:
              Updated:
              Resolved: