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

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

XMLWordPrintableJSON

    • 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
    • Labels:
    • 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: