[SERVER-72753] Incorrect usage of plan cache entry for IXSCAN over partial filter index containing $and Created: 11/Jan/23  Updated: 09/Feb/23  Resolved: 09/Feb/23

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: 5.0.14, 6.0.3, 6.2.0-rc5
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Nicholas Zolnierz Assignee: Nicholas Zolnierz
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Duplicate
duplicates SERVER-68434 Equality to null can incorrectly use ... Closed
Assigned Teams:
Query Optimization
Operating System: ALL
Backport Requested:
v6.0, v5.0, v4.4, v4.2
Steps To Reproduce:

(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);
})();

Sprint: QO 2023-02-06, QO 2023-02-20
Participants:

 Description   

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. 



 Comments   
Comment by Nicholas Zolnierz [ 09/Feb/23 ]

SERVER-68434 ended up with a generic solution that also fixes the issue described by this ticket.

Generated at Thu Feb 08 06:22:42 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.