Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-72753

Incorrect usage of plan cache entry for IXSCAN over partial filter index containing $and

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 5.0.14, 6.0.3, 6.2.0-rc5
    • Component/s: None
    • Labels:
      None
    • Query Optimization
    • ALL
    • v6.0, v5.0, v4.4, v4.2
    • Hide 
      (function() {
"use strict";

db.test.drop();

const indexList = [
    {
        "a": 1,
    },
    {
        "a": -1,
    },
];

const indexOptions = [
    {
        partialFilterExpression: {a: {$gt: 0, $lt: 10}},
    },
    {},
];

const documentList = [
    {
        _id: 1,
        "a": 1,
    },
    {
        _id: 2,
        "a": 11,
    },
];

assert.commandWorked(db.test.insert(documentList));

for (let i = 0; i < indexList.length; i++) {
    db.test.createIndex(indexList[i], indexOptions[i]);
}

// Populate cache for query shape {a: {$gt: <>, $lt: <>}}
assert.eq(db.test.aggregate({$match: {"a": {$gt: 0, $lt: 9}}}).itcount(), 1);
assert.eq(db.test.aggregate({$match: {"a": {$gt: 0, $lt: 9}}}).itcount(), 1);

// The plan cache key for this query ends up matching the first two, and thus incorrectly uses 
// the partial IXSCAN plan. This should match the {_id: 2, a: 11} doc but does not if planned // from cache.
assert.eq(db.test.aggregate({$match: {"a": {$gt: 0, $lt: 12}}}).itcount(), 2);
})();
      Show
      (function() { "use strict"; db.test.drop(); const indexList = [ { "a": 1, }, { "a": -1, }, ]; const indexOptions = [ { partialFilterExpression: {a: {$gt: 0, $lt: 10}}, }, {}, ]; const documentList = [ { _id: 1, "a": 1, }, { _id: 2, "a": 11, }, ]; assert.commandWorked(db.test.insert(documentList)); for (let i = 0; i < indexList.length; i++) { db.test.createIndex(indexList[i], indexOptions[i]); } // Populate cache for query shape {a: {$gt: <>, $lt: <>}} assert.eq(db.test.aggregate({$match: {"a": {$gt: 0, $lt: 9}}}).itcount(), 1); assert.eq(db.test.aggregate({$match: {"a": {$gt: 0, $lt: 9}}}).itcount(), 1); // The plan cache key for this query ends up matching the first two, and thus incorrectly uses // the partial IXSCAN plan. This should match the {_id: 2, a: 11} doc but does not if planned // from cache. assert.eq(db.test.aggregate({$match: {"a": {$gt: 0, $lt: 12}}}).itcount(), 2); })();
    • QO 2023-02-06, QO 2023-02-20

      When computing the plan cache key for a query of the shape {a: {$gt: <>, $lt: <>}}, we look at each individual clause and compute the discriminator for that predicate only. If an index contains a partial filter expression that is able to satisfy the entire query but not each individual predicate, then the discriminator incorrectly indicates that the index is not compatible. (The $gt and $lt are not particularly relevant, just easier to demonstrate the issue)

      The impact of this is that two queries may be considered equivalent from the plan cache perspective, and thus an ineligible plan (incorrect results) gets chosen for a query which is actually not compatible with the partial index. Note that the core planner logic for determining index compatibility is separate from the plan cache key computation. 

            Assignee:
            nicholas.zolnierz@mongodb.com Nicholas Zolnierz
            Reporter:
            nicholas.zolnierz@mongodb.com Nicholas Zolnierz
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: