[SERVER-28503] Date comparisons with null in aggregation do not work as expected Created: 25/Mar/17  Updated: 31/May/17  Resolved: 27/Mar/17

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: 3.2.12, 3.4.0
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Franz Payer Assignee: Mark Agarunov
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Steps To Reproduce:

require('../../utils/lib/preload.js');
 
var config = require('lib/config');
var MongoClient = require('mongodb').MongoClient;
var async = require('async');
 
var mongo;
 
async.waterfall([
  function(d) {
    config.dbOptions = {
      keepAlive : 3000000,
      connectTimeoutMS : 3000000,
      socketTimeoutMS : 3000000,
    };
    MongoClient.connect(config.db || '', config.dbOptions || {}, d);
  },
  function(connection, d) {
    mongo = connection;
    mongo.collection('test').remove();
    d();
  },
  function(d) {
    mongo.collection('test').insert([
      {
        item : 1,
        test : new Date(1), // new Date(1490000000000),
      },
      {
        item : 2,
        test : null,
      },
    ], function(err) {
      d(err);
    });
  },
  function(d) {
    mongo.collection('test').find({ test : { $lt : new Date(100) } }).limit(0).toArray(function(err, results) {
      console.log('Find > 0', err, results);
      d(err);
    });
  },
  function(d) {
    mongo.collection('test').aggregate([
      { $match : { } },
      { $group : {
        _id : '$item',
        test : { $sum : {
          $cond : [
            { $lt : [ '$test', new Date(100) ] },
            1,
            0,
          ],
        } },
      } },
    ], function(err, results) {
      console.log('Aggregate > 0', err, results);
      d(err);
    });
  },
  function(d) {
    mongo.collection('test').find({ test : { $lt : new Date(-1) } }).limit(0).toArray(function(err, results) {
      console.log('Find < 0', err, results);
      d(err);
    });
  },
  function(d) {
    mongo.collection('test').aggregate([
      { $match : { } },
      { $group : {
        _id : '$item',
        test : { $sum : {
          $cond : [
            { $lt : [ '$test', new Date(-1) ] },
            1,
            0,
          ],
        } },
      } },
    ], function(err, results) {
      console.log('Aggregate < 0', err, results);
      d(err);
    });
  },
], function(err) {
  console.log(err);
  mongo.close();
});

Participants:

 Description   

The behavior of the "lt" operator in aggregations appears to be that it yields true when comparing any null value to a date. Look at this example:

doc1 : { item : 1, test : new Date(1) }
doc2 : { item : 2, test : null }


Scenario 1
find with

{ lt : new Date(100) }


Positive match on doc1 only


aggregate with

{ $lt : [ '$test', new Date(100) ] }


Positive match on both doc1 and doc2


Scenario 2
find with

{ lt : new Date(-1) }


No positive match


aggregate with

{ $lt : [ '$test', new Date(-1) ] }


Positive match on doc2 only


I originally thought that scenario 1 could be explained by the value of null being 0, which is why the aggregate matched doc2. However, I could not think of a reason why doc2 is being matched in scenario 2. I am assuming the difference in behavior between find and aggregate within each scenario is due to the additional checks with find.

This feels like unexpected behavior to me, but please correct me if I am wrong.



 Comments   
Comment by Asya Kamsky [ 27/Mar/17 ]

Hi fpayer,

The docs page that describes the sort order is this one: https://docs.mongodb.com/manual/reference/bson-type-comparison-order/

You can also see the actual sort order in the source code here:
https://github.com/mongodb/mongo/blob/master/src/mongo/bson/bsontypes.h#L124-L168

Asya

Comment by Franz Payer [ 27/Mar/17 ]

Hi Mark,

The bson types page, https://docs.mongodb.com/manual/reference/bson-types, lists null with a value of 10 while Date has a value of 9 so this behavior still seems incorrect as null should never be less than Date when comparing BSON types. If this is still expected behavior, I apologize for using your time and you can close this issue.

Best,
Franz

Comment by Mark Agarunov [ 27/Mar/17 ]

Hello fpayer,

Thank you for the report. What you are seeing may be expected behavior. When using aggregation, comparisons use BSON type ordering, while find does not compare types.

According to the documentation for the aggregation operators:

"For most data types, comparison operators only perform comparisons on fields where the BSON type matches the query value’s type. MongoDB supports limited cross-BSON comparison through Type Bracketing."

Please note that SERVER project is for reporting bugs or feature suggestions for the MongoDB server. For MongoDB-related support discussion please post on the mongodb-user group or Stack Overflow with the mongodb tag. A question like this involving more discussion would be best posted on the mongodb-user group.

Thanks,
Mark

Comment by Franz Payer [ 25/Mar/17 ]

My apologies for the formatting in the original issue. Hopefully, this is more readable:

doc1: { item : 1, test : new Date(1) }
doc2: { item : 2, test : null }
 
Scenario 1

find with { lt : new Date(100) }
Positive match on doc1 only

 
aggregate with { $lt : [ '$test', new Date(100) ] }
Positive match on both doc1 and doc2
 
Scenario 2

find with { lt : new Date(-1) }
No positive match

 
aggregate with { $lt : [ '$test', new Date(-1) ] }
Positive match on doc2 only

Generated at Thu Feb 08 04:18:19 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.