Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-61592

Querying with ms precision does not return expected results (TS collection)

    • Type: Icon: Bug Bug
    • Resolution: Fixed
    • Priority: Icon: Major - P3 Major - P3
    • 5.3.0, 5.2.1, 5.0.7
    • Affects Version/s: None
    • Component/s: None
    • Fully Compatible
    • ALL
    • v5.2, v5.0
    • Hide
      • 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")
                                        }
                                    }
                                ]
                            } ... 
        
      Show
      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" ) } } ] } ...
    • QO 2022-01-10, QO 2022-01-24

      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

            Assignee:
            james.wahlin@mongodb.com James Wahlin
            Reporter:
            michael.gargiulo@mongodb.com Michael Gargiulo
            Votes:
            0 Vote for this issue
            Watchers:
            10 Start watching this issue

              Created:
              Updated:
              Resolved: