In MongoDB I would like to use $gt and $lt comparision operators where the value could be null. When the operators did not work with null, I looked for documentation but found none. In both cases it returned no documents (even though $ne, $gte, and $lte did return documents; meaning there were documents that were both equal to and not equal to null).
I would expect $gt to essentially operate like $ne (as the null type Mongo comarison order is so low) and $lt to return nothing for the same reason.
I was hoping this would work as the value I pass to the query is variable (potentially null), and I don't want to have to write a special case for null.
Example of what I was expeccting, given the following collection:
{
id: 1,
colNum: null
}
{
id: 2,
colNum: 72
}
{
id: 3
}
I would expect the following query:
db.testtable.find( { "colNum" { $gt : null } } )
To return:
{
id: 2,
colNum: 72
}
However, nothing was returned.
Is there a reason that $gt and $lt don't seem to work with null, or is it a MongoDB bug, or is it actually supposed to work and there is likely a user error?
Nitty-Gritty Details
Reading through the latest Mongo source, there's basically 2 cases when doing comparisons involving
null
:==
,>=
,<=
) of null & undefined will returntrue
; otherwise any comparison withnull
will returnfalse
.Note: No other BSON type has the same canonical type as
null
.null
), then compareElementValues is called. Fornull
, this just returns the difference between the canonical type of both BSON elements and then carries out the requested comparison against0
.For example,
null > null
would translate into(5-5) > 0 --> False
because the canonical type of null is 5.Similarly,
null < null
would translate into(5-5) < 0 --> False
.This means
null
can only ever be equal tonull
orundefined
. Any other comparison involvingnull
will always returnfalse
.Is this a Bug?
Updated Answer:
The documentation for the comparison operators ($gt, $lt) references the documentation which you originally linked, which implies that the comparison operators should work with
null
. Furthermore, query sorting (i.e.,db.find().sort()
) does accurately follow the documented Comparison/Sort behavior.This is, at the very least, inconsistent. I think it would be worth submitting a bug report to MongoDB's JIRA site.
Original Answer:
I don't think this behavior is a bug.
The general consensus for Javascript is that
undefined
means unassigned whilenull
means assigned but otherwise undefined. Value comparisons against undefined, aside from equality, don't make sense, at least in a mathematical sense.Given that BSON draws heavily from JavaScript, this applies to MongoDB too.