[SERVER-36635] Inconsistent query results with and without presence of partial indexes Created: 14/Aug/18  Updated: 07/Jun/23

Status: Backlog
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Critical - P2
Reporter: Ian Boros Assignee: Backlog - Query Execution
Resolution: Unresolved Votes: 1
Labels: mql-semantics, query-44-grooming
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to SERVER-36681 Change {$ne: null} semantics to be mo... Backlog
Assigned Teams:
Query Execution
Operating System: ALL
Participants:

 Description   

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}".



 Comments   
Comment by Asya Kamsky [ 17/Aug/18 ]

charlie.swanson amended with version - it is fixed in 4.1.1.

However, original behavior is not about existence of a partial index - it's about the fact that our query semantics don't treat {foo:{$ne:null} as subset of {foo:{$exists:true} which is SERVER-36681.

I think this is a duplicate of SERVER-36681 - the fact that something can be not equal to null but at the same time not considered to exist.

Comment by Charlie Swanson [ 17/Aug/18 ]

asya can you test that again after SERVER-27646? I think I may have fixed this during that work. I think the fix was that we realized the index scan was incorrect on multikey indexes with dotted paths, so we would do a collection scan instead.

Comment by Asya Kamsky [ 17/Aug/18 ]

This is not specific to partial indexes in versions before 4.1.1:

db.c.find()
{ "_id" : ObjectId("5b47ee480e2b9943cf41894b"), "a" : 4, "b" : 10 }
{ "_id" : ObjectId("5b47ee4f0e2b9943cf41894c"), "a" : [ 3, 5 ], "b" : 3 }
db.c.find({"a.b":{$exists:true}})
db.c.find({"a.b":{$exists:false}})
{ "_id" : ObjectId("5b47ee480e2b9943cf41894b"), "a" : 4, "b" : 10 }
{ "_id" : ObjectId("5b47ee4f0e2b9943cf41894c"), "a" : [ 3, 5 ], "b" : 3 }
db.c.find({"a.b":{$eq:null}})
{ "_id" : ObjectId("5b47ee480e2b9943cf41894b"), "a" : 4, "b" : 10 }
db.c.find({"a.b":{$ne:null}})
{ "_id" : ObjectId("5b47ee4f0e2b9943cf41894c"), "a" : [ 3, 5 ], "b" : 3 }
db.c.createIndex({"a.b":1})
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}
db.c.find({"a.b":{$exists:true}})
db.c.find({"a.b":{$exists:false}})
{ "_id" : ObjectId("5b47ee480e2b9943cf41894b"), "a" : 4, "b" : 10 }
{ "_id" : ObjectId("5b47ee4f0e2b9943cf41894c"), "a" : [ 3, 5 ], "b" : 3 }
db.c.find({"a.b":{$eq:null}})
{ "_id" : ObjectId("5b47ee480e2b9943cf41894b"), "a" : 4, "b" : 10 }
db.c.find({"a.b":{$ne:null}})
// nothing returned

This is with regular index against 4.0.

Generated at Thu Feb 08 04:43:41 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.