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

Inconsistent query results with and without presence of partial indexes

XMLWordPrintableJSON

    • Query Optimization
    • Fully Compatible
    • ALL
    • v8.1, v8.0, v7.0, v6.0
    • None
    • 0
    • None
    • None
    • None
    • None
    • None
    • None

      A fun fact about our query language which I recently learned is that {$ne: null} doesn't necessarily return a subset of what {$exists: true} returns:

      db.c.insert({a: [1, {c: 1}]});
db.c.insert({a: [1]});
 
db.c.find({"a.b": {$exists: true}}) // Returns nothing.
db.c.find({"a.b": {$ne: null}}) // Returns {a: [1]} !!!!

      When we create a partial index using a filter {$exists: true}, we do not respect these semantics for {$ne: null}:

      db.d.drop();
db.d.createIndex(
   { "a.b": 1 },
   { partialFilterExpression: { "a.b": { $exists: true } } }
);
db.d.insert({a: [1]});

const query = {"a.b": {$ne: null}};
print("Running the query " + tojson(query) + " with a partial index present");
print("Results are " + tojson(db.d.find(query).toArray())); // (1)

print("Dropping the index");
db.d.dropIndexes();
print("Results from the same query are now " + tojson(db.d.find(query).toArray())); // (2)

      The results from (1) will be different than those from (2).

      I think the bug is here. This code basically says "{$ne: null} is a subset of {$exists true}".

            Assignee:
            naafiyan.ahmed@mongodb.com Naafiyan Ahmed
            Reporter:
            ian.boros@mongodb.com Ian Boros
            Votes:
            1 Vote for this issue
            Watchers:
            27 Start watching this issue

              Created:
              Updated:
              Resolved: