[SERVER-81048] Possible Date Precision Bug in 6.0.10 Created: 13/Sep/23  Updated: 27/Oct/23  Resolved: 28/Sep/23

Status: Closed
Project: Core Server
Component/s: Query Execution
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Minor - P4
Reporter: Patrick Meredith Assignee: Projjal Chanda
Resolution: Works as Designed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Related
related to SERVER-77615 Date subtract operation doesn't work ... Closed
Assigned Teams:
Query Execution
Operating System: ALL
Sprint: QE 2023-10-02
Participants:

 Description   

This error came up in our normal BI Connector tdvt waterfall tests.

If this is an intentional change, we can fix the tests on our end, but there has been a change in date math between 6.0.9 and 6.0.10.

This is only occurring on 6.0.10, however, while working fine in 6.2 (we are not yet running this test on 7.0).

I've reduced the test case down here to a fairly minimal query. The sort is just to make the results easier to compare.

The dataset itself is encoded in the test. $num4 is is a double that is conditionally $floored or $ceil'd and subtracted from the $date0 field, which is a date. I can confirm all the $floor and $ceil calculations are the same between versions, so it's coming down to date $subtract.

The outputs are differing by .001 second for two of the five documents in the result

  db = connect( 'mongodb://localhost/fullblackbox' )
  printjson(
  db.Calcs.aggregate(
  [{"$project":
  {"date0": 1,
   "num4": 1,
   "out": {"$let": {
          "vars": {"date": {
                      "$subtract": ["$date0",{"$multiply": [
                          {"$cond": {"if": {"$lt": [{"$floor": ["$num4"]},{"$literal": 0.0}]},
                              "then": {"$ceil": [{"$subtract": [{"$floor": ["$num4"]},0.5]}]},
                              "else": {"$floor": [{"$add": [{"$floor": ["$num4"]},0.5]}]}}},
                              {"$literal": NumberInt("86400000")}]}]}},
          "in": {"$cond": {"if": {"$lte": ["$$date",{"$literal": null}]},
                         "then": {"$literal": null},
                         "else": {"$subtract": ["$$date",{"$multiply": [{"$literal": NumberLong("86400")},
                                                         {"$subtract": ["$num4",{"$floor": ["$num4"]}]},{"$literal": NumberInt("1000")}]}]}}}
          }},
   "_id": NumberInt("0")}
  },
  {$sort: {"date0": 1}}
  ]
  )
  ) 

 

The results for 6.0.9 are:

    1 [
    2   {
    3     num4: 10.85,
    4     date0: ISODate("1972-07-04T00:00:00.000Z"),
    5     out: ISODate("1972-06-23T03:36:00.001Z")
    6   },
    7   {
    8     num4: -13.47,
    9     date0: ISODate("1975-11-12T00:00:00.000Z"),
   10     out: ISODate("1975-11-25T11:16:48.001Z")
   11   },
   12   { num4: null, date0: ISODate("2004-04-15T00:00:00.000Z"), out: null },
   13   {
   14     num4: -6.05,
   15     date0: ISODate("2004-06-04T00:00:00.000Z"),
   16     out: ISODate("2004-06-10T01:12:00.000Z")
   17   },
   18   {
   19     num4: 8.32,
   20     date0: ISODate("2004-06-19T00:00:00.000Z"),
   21     out: ISODate("2004-06-10T16:19:12.000Z")
   22   }
   23 ] 

While the results for 6.0.10 are:

    1 [
    2   {
    3     num4: 10.85,
    4     date0: ISODate("1972-07-04T00:00:00.000Z"),
    5     out: ISODate("1972-06-23T03:36:00.000Z")
    6   },
    7   {
    8     num4: -13.47,
    9     date0: ISODate("1975-11-12T00:00:00.000Z"),
   10     out: ISODate("1975-11-25T11:16:48.000Z")
   11   },
   12   { num4: null, date0: ISODate("2004-04-15T00:00:00.000Z"), out: null },
   13   {
   14     num4: -6.05,
   15     date0: ISODate("2004-06-04T00:00:00.000Z"),
   16     out: ISODate("2004-06-10T01:12:00.000Z")
   17   },
   18   {
   19     num4: 8.32,
   20     date0: ISODate("2004-06-19T00:00:00.000Z"),
   21     out: ISODate("2004-06-10T16:19:12.000Z")
   22   }
   23 ] 



 Comments   
Comment by Patrick Meredith [ 02/Oct/23 ]

projjal.chanda@mongodb.com thank you, I thought this may be "works as designed". We'll update the test so this isn't flagged in the future.

Comment by Projjal Chanda [ 28/Sep/23 ]

Hi patrick.meredith@mongodb.com. This is due to an expected behavior change from SERVER-75287 where the subtract behavior is made consistent with add (in this case in that the double result is being rounded to long instead of being truncated)

Comment by Amr Elhelw [ 19/Sep/23 ]

Seems related to SERVER-77615.
projjal.chanda@mongodb.com Could you please take a look and confirm?

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