[SERVER-66469] Filtering timeseries with date-field does not include results from before 1970 Created: 15/May/22  Updated: 29/Oct/23  Resolved: 27/Sep/22

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: 5.0.7
Fix Version/s: 6.2.0-rc0, 6.0.5, 5.0.16

Type: Bug Priority: Major - P3
Reporter: Stefan de Jong Assignee: Joel Redman (Inactive)
Resolution: Fixed Votes: 0
Labels: time-series
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Depends
depends on SERVER-66794 Add an in-memory flag for TS collecti... Closed
is depended on by SERVER-73641 Timeseries filtering can miss extende... Open
Documented
is documented by DOCS-15650 [Server] Investigate changes in SERVE... Closed
Related
related to SERVER-66727 Time-series bucket can be created tha... Closed
related to SERVER-67825 Investigate where time-series functio... Closed
is related to SERVER-69952 Filtering on dates outside of the 32 ... Closed
Backwards Compatibility: Fully Compatible
Operating System: ALL
Backport Requested:
v6.0, v5.0
Steps To Reproduce:
  • Create a time-series collection
  • Insert records before and after epoch
  • Query for documents with filter: { $timeField: { $lt: "arbitrary date > 1970" }

    }

  • Results will only include documents after epoch

 

If you query with { $timeField:

{ $lt: "arbitrary date < 1970" }

}, the documents missing from the previous set will suddenly show up.

Sprint: QO 2022-09-19, QO 2022-10-03
Participants:

 Description   

db['test-array-ts'].countDocuments({
  date: { $lt: ISODate('1970-01-01T18:31:22.000+00:00') }
})
result: 149

db['test-array-ts'].countDocuments({
  date: { $lt: ISODate('1965-01-01T18:31:22.000+00:00') }
})
result: 1989943



 Comments   
Comment by Githook User [ 15/Mar/23 ]

Author:

{'name': 'Joel Redman', 'email': 'joel.redman@mongodb.com', 'username': 'joredman'}

Message: SERVER-66469 Fix correctness when filtering on dates before the epoch

(cherry picked from commit aedaf14d6dcc60253f3944252eb5cfc588e08ecb)
Branch: v5.0
https://github.com/mongodb/mongo/commit/21ee6003fe704534e63ea20d5aa0cf563da0df2f

Comment by Githook User [ 21/Feb/23 ]

Author:

{'name': 'Joel Redman', 'email': 'joel.redman@mongodb.com', 'username': 'joredman'}

Message: SERVER-66793 Use explicit sort when extended range dates are used

(cherry picked from commit 2b65016588fc5f868b0e396b64d1fe9da916a343)

Also includes some changes from aedaf14d6dcc60253f3944252eb5cfc588e08ecb:
plumbing the 'usesExtendedRange' flag and making it available on
BucketSpec. This was originally part of SERVER-66469.
Branch: v5.0
https://github.com/mongodb/mongo/commit/4e9ee688d44e57c1a784e9b146f1d456a0fd9b29

Comment by Githook User [ 31/Jan/23 ]

Author:

{'name': 'Joel Redman', 'email': 'joel.redman@mongodb.com', 'username': 'joredman'}

Message: SERVER-66469 Fix correctness when filtering on dates before the epoch
Branch: v6.0
https://github.com/mongodb/mongo/commit/aedaf14d6dcc60253f3944252eb5cfc588e08ecb

Comment by Githook User [ 27/Sep/22 ]

Author:

{'name': 'Joel Redman', 'email': 'joel.redman@mongodb.com', 'username': 'joredman'}

Message: SERVER-66469 Fix correctness when filtering on dates before the epoch
Branch: master
https://github.com/mongodb/mongo/commit/03d5afb49317b85f322ff8f902e72037467cdbcd

Comment by Joel Redman (Inactive) [ 23/Sep/22 ]

SERVER-69952 is about the literals being compared, SERVER-66469 is about the collection itself.

Comment by Chris Kelly [ 24/May/22 ]

Hi Stefan,

I was able to reproduce this behavior in MongoDB 5.0.7 by referencing the example time series collection and data shown here. I modified this data to add your dates. If I insert the two dates you mention (one in 1970, and one in 1965) and attempt to run:

 

db['weather'].countDocuments({
date:
{ $lt: ISODate('1970-02-01T18:31:22.000+00:00') }
})

By incrementing the date by 1 month, I will only get 1 document. If the date is equal to the one inserted, the return value is 0.

If I do this again for the older date, the same phenomenon occurs: if it's equal, it returns zero. If it's slightly above, it returns 1.

db['weather'].countDocuments({
date:
{ $lt: ISODate('1965-02-01T18:31:22.000+00:00') }
})

 

The first query I list should be returning 2 in this case, since there are 2 documents with a date before 1970-02-01T18:31:22.000+00:00. You would expect this to work since it is internally represented as a signed 64-bit integer representing the number of milliseconds since the Unix epoch (Jan 1, 1970). While the docs mention that not all database operations support the full 64-bit range,  it says: "you may safely work with dates with years within the inclusive range 0 through 9999."

Given this information, I'm going to forward this to Query Execution for further investigation. Thank you for your report.

Regards,
Christopher

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