[SERVER-61592] Querying with ms precision does not return expected results (TS collection) Created: 18/Nov/21  Updated: 29/Oct/23  Resolved: 19/Jan/22

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: 5.3.0, 5.2.1, 5.0.7

Type: Bug Priority: Major - P3
Reporter: Michael Gargiulo Assignee: James Wahlin
Resolution: Fixed Votes: 0
Labels: time-series
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Backwards Compatibility: Fully Compatible
Operating System: ALL
Backport Requested:
v5.2, v5.0
Steps To Reproduce:
  • Create a TS collection 

db.createCollection( "weather", { timeseries: { timeField: "timestamp", metaField: "metadata" } } ) 

  • Insert the below document 

    db.weather.insertOne(
        {
            "timestamp": new Date("2021-05-18T00:00:00.000+00:00")
            ,
            "metadata": {
                "sensorId": "6666",
                "type": "temperature"
            },
            "temp": 14
        }
    )
    

  • Issue the below query which unexpectedly returns an empty result set 

    db.weather.find(
        {"timestamp": {$lt: new Date("2021-05-18T00:00:00.001+00:00")}}
    )
    

  • Examine the below explain 

    {
        "explainVersion" : "1",
        "stages" : [ 
            {
                "$cursor" : {
                    "queryPlanner" : {
                        "namespace" : "mydatabase.system.buckets.weather",
                        "indexFilterSet" : false,
                        "parsedQuery" : {
                            "$and" : [ 
                                {
                                    "_id" : {
                                        "$lt" : ObjectId("60a303800000000000000000")
                                    }
                                }, 
                                {
                                    "control.min.timestamp" : {
                                        "$_internalExprLt" : ISODate("2021-05-18T00:00:00.001Z")
                                    }
                                }
                            ]
                        } ... 
    

Sprint: QO 2022-01-10, QO 2022-01-24
Participants:

 Description   

Summary: 
When issuing a query on a timeseries collection using ms precision, the query rewrite of the match on the buckets collection uses the ObjectId and second precision with a $lt. This effectively, truncates the ms and ignores the requested precision and does not return expected results. 

Notes:
Since we are evaluating ObjectId with a second precision I believe we should be using $gt in the rewrite for logical $lt queries issued by users



 Comments   
Comment by Githook User [ 27/Jan/22 ]

Author:

{'name': 'James Wahlin', 'email': 'james@mongodb.com', 'username': 'jameswahlin'}

Message: SERVER-61592 Correctness fix for time-series query with millisecond granularity

Prior to this fix a time-series collection query on time would truncate the
requested time to second granularity and could miss metrics that fall within
the requested time and the truncated value

(cherry picked from commit 0073acf24096185e67c87318919354b4745cd538)
Branch: v5.2
https://github.com/mongodb/mongo/commit/c6f9b9144d3cf2eff24c28892d956d2687f00267

Comment by Githook User [ 27/Jan/22 ]

Author:

{'name': 'James Wahlin', 'email': 'james@mongodb.com', 'username': 'jameswahlin'}

Message: SERVER-61592 Correctness fix for time-series query with millisecond granularity

Prior to this fix a time-series collection query on time would truncate the
requested time to second granularity and could miss metrics that fall within
the requested time and the truncated value

(cherry picked from commit 0073acf24096185e67c87318919354b4745cd538)
Branch: v5.0
https://github.com/mongodb/mongo/commit/14eac7b0e7ef39ddd3d01ee3bada08514e3be6b0

Comment by Githook User [ 19/Jan/22 ]

Author:

{'name': 'James Wahlin', 'email': 'james@mongodb.com', 'username': 'jameswahlin'}

Message: SERVER-61592 Correctness fix for time-series query with millisecond granularity

Prior to this fix a time-series collection query on time would truncate the
requested time to second granularity and could miss metrics that fall within
the requested time and the truncated value
Branch: master
https://github.com/mongodb/mongo/commit/0073acf24096185e67c87318919354b4745cd538

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