Can't create covered query with nulls

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.

Hi @_Voronenkov,

Welcome to MongoDB community!

This is not a bug, covered queries are not performed for null predict values:
https://docs.mongodb.com/v4.2/core/query-optimization/#covered-query

So it is working as designed.

Thanks
Pavel

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.