Why does MongoDB (version 4.2.8) handle queries with and without null differently?
Sample:
db.b.insertMany([
{c:"a", },
{c:"a", },
{c:"a", n:null},
{c:"a", n:1},
{c:"a", n:10},
{c:"a", n:5},
{c:"a", n:1},
{c:"a", n:12},
{c:"b", n:12},
]);
db.b.createIndex({"n": 1, "c": 1});
Then if I call
db.b.find({c: "a", n: {$in:[10, 12]}}, {_id: 0, n:1,c:1}).explain("allPlansExecution")
the query is fully covered. But, the query
db.b.find({c: "a", n: {$in:[null, 12]}}, {_id: 0, n:1,c:1}).explain("allPlansExecution")
isn’t covered, and MongoDB fetches all found documents.
It looks like a bug in MongoDB.