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
:
- If the canonical types of the BSON elements being compared are different, only equality comparisons (
==
, >=
, <=
) of null & undefined will return true
; otherwise any comparison with null
will return false
.
Note: No other BSON type has the same canonical type as null
.
- If the canonical types are the same (i.e., both elements are
null
), then compareElementValues is called. For null
, this just returns the difference between the canonical type of both BSON elements and then carries out the requested comparison against 0
.
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 to null
or undefined
. Any other comparison involving null
will always return false
.
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 while null
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.