14

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?

TryThis
  • 354
  • 2
  • 11

1 Answers1

20

Nitty-Gritty Details

Reading through the latest Mongo source, there's basically 2 cases when doing comparisons involving null:

  1. 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.
  2. 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.

Community
  • 1
  • 1
cyfur01
  • 3,262
  • 1
  • 34
  • 38
  • Thanks for the detailed answer. That was exactly what I was looking for. I'm not sure if I completely agree with the bug section though. I understand the historical relationship with JavaScript but they have since diverged a bit. Mongo has deprecated _Undefined_. Also in the Mongo documentation I reference the call it a _Comparison_ /Sort Order. Anybody have thoughts on this? – TryThis Apr 24 '15 at 20:14
  • I had missed your link to the documentation. What the documentation says *does* accurately apply to sorting (_i.e._, `db.find().sort()`), but *not* to comparison operators in queries (_i.e._, `$gt`, `$lt`, _etc._). That certainly is confusing. – cyfur01 Apr 26 '15 at 01:42
  • Note the `$lt` and `$gt` operators _in the aggregation pipeline_ act differently than `$lt` and `$gt` in normal query operators. In the aggregation pipeline `undefined` values are "less that" `null`. This is not true in standard queries. Thanks Mongo. – Molomby Sep 21 '18 at 05:48