Plan cache entry ignores index collation with $elemMatch, causing incorrect query results

XMLWordPrintableJSON

    • Query Optimization
    • Fully Compatible
    • ALL
    • v8.0, v7.0, v6.0, v5.0
    • Hide 
      const conn = MongoRunner.runMongod();
const db = conn.getDB(jsTestName());

const documentList = [
    {_id: 47, "obj": {"array": ["lowercase"]}},
    {_id: 78, "obj": {"array": ["lowercase"]}},
];

let coll = db.bf34252;
coll.drop();
assert.commandWorked(coll.insert(documentList));

// Create two indexes and run the query twice to create an _active_ plan cache entry.
assert.commandWorked(coll.createIndex({"obj.array": 1}, {
    collation: {locale: 'en'},
}));
assert.commandWorked(coll.createIndex({"obj.array": 1, unused: 1}, {
    collation: {locale: 'en'},
}));

// Sanity check
const match2 = {"obj.array": {$elemMatch: {$gte: "UPPERCASE"}}};
assert.eq(2, coll.find(match2).itcount());

for (let i = 0; i < 2; ++i) {
    coll.find({"obj.array": {$elemMatch: {$gte: NumberLong("78219")}}}).itcount();
}

assert.eq(2, coll.find(match2).itcount()); // <---- THIS ASSERTION FAILS

MongoRunner.stopMongod(conn);
      Show
      const conn = MongoRunner.runMongod(); const db = conn.getDB(jsTestName()); const documentList = [ {_id: 47, "obj" : { "array" : [ "lowercase" ]}}, {_id: 78, "obj" : { "array" : [ "lowercase" ]}}, ]; let coll = db.bf34252; coll.drop(); assert.commandWorked(coll.insert(documentList)); // Create two indexes and run the query twice to create an _active_ plan cache entry. assert.commandWorked(coll.createIndex({ "obj.array" : 1}, { collation: {locale: 'en' }, })); assert.commandWorked(coll.createIndex({ "obj.array" : 1, unused: 1}, { collation: {locale: 'en' }, })); // Sanity check const match2 = { "obj.array" : {$elemMatch: {$gte: "UPPERCASE" }}}; assert.eq(2, coll.find(match2).itcount()); for (let i = 0; i < 2; ++i) { coll.find({ "obj.array" : {$elemMatch: {$gte: NumberLong( "78219" )}}}).itcount(); } assert.eq(2, coll.find(match2).itcount()); // <---- THIS ASSERTION FAILS MongoRunner.stopMongod(conn);
    • QO 2024-08-05
    • 200
    • None
    • 3
    • None
    • None
    • None
    • None
    • None
    • None

      Issue Status as of 06/17/2025

      SUMMARY
      On MongoDB server versions 6.0.0-6.0.23, 7.0.0-7.0.20, and 8.0.0-8.0.9, it is possible for $elemMatch with predicates affected by collation to use an index with incompatible collation due to a plan cache bug. Impacted queries can return results which miss documents that should have been included. Write operations that rely on queries that incorrectly miss documents may not correctly update those documents. This includes updates, deletes, and $merge/$out aggregations with impacted query filters. A subset of affected operations may instead assert during query planning.

      ISSUE DESCRIPTION AND IMPACT

      The issue occurs when there are (at least) two $elemMatch queries with the same query shape using the same collation, where one $elemMatch is collation-insensitive (i.e., is not affected by collation, like a match on a numeric value) and one is collation-sensitive (i.e., is affected by collation, like a match on a string value). If there is an index on the same field with a different collation, the first query is eligible to use the index, but the second query should not be eligible.

      For example, given an index on field “a” with no collation specified, the following query may use the index:

      db.collection.find({"a": {$elemMatch: {$gte: 123, $lte: 125}}})
             .collation({locale: 'en', strength: 1, numericOrdering: true})

      The following query has the same query shape, but it should not use the index:

      db.collection.find({"a": {$elemMatch: {$gte: "á", $lte: "é"}}})
             .collation({locale: 'en', strength: 1, numericOrdering: true})

      A problem occurs if the first query creates a plan cache entry using the index with a different collation. If the second query has the same plan cache key, then it may erroneously use the incompatible index. This may result in assertions during query planning or incorrect query results. The incorrect query results can manifest as missing documents in query results or missing writes – documents which should have been updated or deleted were not, or documents which should have been inserted via $out/$merge were not.

      DIAGNOSIS AND AFFECTED VERSIONS

      Users running queries as described above prior to MongoDB 6.0.24, 7.0.21, or 8.0.10 may have been affected.

      To confirm if the issue currently impacts a particular query, users can run the query via the “explain” command and observe the “planCacheKey” field. Then, users should observe the plans currently in the plan cache, for example via $planCacheStats. If there is an entry in the plan cache for the query’s planCacheKey, and if that entry represents a plan that uses an index with a different collation to answer the collation-sensitive $elemMatch, then the query is impacted. Note: explain output alone is not enough to determine if a query is currently impacted, because explain commands bypass the plan cache.

      REMEDIATION AND WORKAROUNDS

      Customers are recommended to upgrade to v6.0.24+, v7.0.21+, or v8.0.10+. For MongoDB Atlas Customers, your Atlas clusters will be upgraded automatically to the version containing the fix.

      A workaround exists without upgrading the cluster. Users can instead add a hint() to the affected query, which causes the correct index to be used. On v8.0, customers can use Query Settings to avoid having to change application code, however it is important to note that the query settings would apply to all queries with a matching query shape. That includes both the collation-sensitive query and the collation-insensitive query.

      —-----------------------------------------------------

      Original description

      The plan cache key should distinguish between two queries with predicates which differ in index compatibility due to index collation. This does not happen when $elemMatch is present in the predicate.

            Assignee:
            Hana Pearlman
            Reporter:
            Jess Balint
            Votes:
            0 Vote for this issue
            Watchers:
            21 Start watching this issue

              Created:
              Updated:
              Resolved: