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

Equivalent find and agg queries produce different plans and different results from a decimal field

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: Query Execution
    • None
    • Query Execution
    • ALL
    • Hide
      use test;
      db.coll.insertOne( {_id: 0, "num": NumberDecimal("42")} );
      
      pipe = [{"$project":{"zzz":{"$add":["$num",{"$pow":[3,-2]}]}}}];
      flt = {};
      prj = {"zzz":{"$add":["$num",{"$pow":[3,-2]}]}};
      
      db.coll.aggregate(pipe);
      { "_id" : 0, "zzz" : NumberDecimal("42.111111111111111") }
      
      db.coll.find(flt, prj);
      { "_id" : 0, "zzz" : NumberDecimal("42.11111111111111110494320541874913") }
      

      The plan of the aggregate query is:

      {
              "stage" : "PROJECTION_DEFAULT",
              "transformBy" : {
                      "zzz" : {
                              "$add" : [
                                      "$num",
                                      {
                                              "$pow" : [
                                                      3,
                                                      -2
                                              ]
                                      }
                              ]
                      }
              },
              "inputStage" : {
                      "stage" : "COLLSCAN",
                      "direction" : "forward"
              }
      }
      

      The plan of the find query is:

      {
              "queryPlan" : {
                      "stage" : "PROJECTION_DEFAULT",
                      "planNodeId" : 2,
                      "transformBy" : {
                              "_id" : true,
                              "zzz" : {
                                      "$add" : [
                                              "$num",
                                              {
                                                      "$const" : 0.1111111111111111
                                              }
                                      ]
                              }
                      },
                      "inputStage" : {
                              "stage" : "COLLSCAN",
                              "planNodeId" : 1,
                              "filter" : {
      
                              },
                              "direction" : "forward"
                      }
              },
              "slotBasedPlan" : {
                      "slots" : "$$RESULT=s9 $$RID=s5 env: { s1 = TimeZoneDatabase(Asia/Yakutsk...Pacific/Ponape) (timeZoneDB), s2 = Nothing (SEARCH_META), s3 = 1644497810697 (NOW) }",
                      "stages" : "[2] traverse s9 s8 s4 [s5] {} {} \nfrom \n    [1] scan s4 s5 none none none none [] @\"c65a4a87-40e7-4f12-84a6-f98725eefcdc\" true false \nin \n    [2] mkbson s8 s4 [_id] keep [zzz = s7] true false \n    [2] project [s7 = let [l1.0 = s6, l1.1 = 0.111111] let [l2.0 = isDate (l1.1), l2.1 = isDate (l1.0)] if (! exists (l1.0) || typeMatch (l1.0, 0x00000440) || ! exists (l1.1) || typeMatch (l1.1, 0x00000440), null, if (! isNumber (l1.0) && ! isDate (l1.0) || ! isNumber (l1.1) && ! isDate (l1.1), fail ( 4974201 ,only numbers and dates are allowed in an $add expression), if (l2.1 && l2.0, fail ( 4974202 ,only one date allowed in an $add expression), if (l2.1 || l2.0, doubleDoubleSum (l1.0, l1.1), l1.0 + l1.1))))] \n    [2] project [s6 = getField (s4, \"num\")] \n    [2] limit 1 \n    [2] coscan \n"
              }
      }
      
      
      Show
      use test; db.coll.insertOne( {_id: 0, "num" : NumberDecimal( "42" )} ); pipe = [{ "$project" :{ "zzz" :{ "$add" :[ "$num" ,{ "$pow" :[3,-2]}]}}}]; flt = {}; prj = { "zzz" :{ "$add" :[ "$num" ,{ "$pow" :[3,-2]}]}}; db.coll.aggregate(pipe); { "_id" : 0, "zzz" : NumberDecimal( "42.111111111111111" ) } db.coll.find(flt, prj); { "_id" : 0, "zzz" : NumberDecimal( "42.11111111111111110494320541874913" ) } The plan of the aggregate query is: { "stage" : "PROJECTION_DEFAULT" , "transformBy" : { "zzz" : { "$add" : [ "$num" , { "$pow" : [ 3, -2 ] } ] } }, "inputStage" : { "stage" : "COLLSCAN" , "direction" : "forward" } } The plan of the find query is: { "queryPlan" : { "stage" : "PROJECTION_DEFAULT" , "planNodeId" : 2, "transformBy" : { "_id" : true , "zzz" : { "$add" : [ "$num" , { "$ const " : 0.1111111111111111 } ] } }, "inputStage" : { "stage" : "COLLSCAN" , "planNodeId" : 1, "filter" : { }, "direction" : "forward" } }, "slotBasedPlan" : { "slots" : "$$RESULT=s9 $$RID=s5 env: { s1 = TimeZoneDatabase(Asia/Yakutsk...Pacific/Ponape) (timeZoneDB), s2 = Nothing (SEARCH_META), s3 = 1644497810697 (NOW) }" , "stages" : "[2] traverse s9 s8 s4 [s5] {} {} \nfrom \n [1] scan s4 s5 none none none none [] @\" c65a4a87-40e7-4f12-84a6-f98725eefcdc\ " true false \nin \n [2] mkbson s8 s4 [_id] keep [zzz = s7] true false \n [2] project [s7 = let [l1.0 = s6, l1.1 = 0.111111] let [l2.0 = isDate (l1.1), l2.1 = isDate (l1.0)] if (! exists (l1.0) || typeMatch (l1.0, 0x00000440) || ! exists (l1.1) || typeMatch (l1.1, 0x00000440), null , if (! isNumber (l1.0) && ! isDate (l1.0) || ! isNumber (l1.1) && ! isDate (l1.1), fail ( 4974201 ,only numbers and dates are allowed in an $add expression), if (l2.1 && l2.0, fail ( 4974202 ,only one date allowed in an $add expression), if (l2.1 || l2.0, doubleDoubleSum (l1.0, l1.1), l1.0 + l1.1))))] \n [2] project [s6 = getField (s4, \" num\ ")] \n [2] limit 1 \n [2] coscan \n" } }
    • 20

      The following two equivalent queries produce a different result, and different plans. The aggregation query constant-folds the expression, and chooses an SBE plan, while the find query doesn't constant-fold, and produces a different result.

      The different results have been found by the fuzzer test from BF-23998.

       

      The problem seems to be at few levels:

      • Constant folding should have worked in both cases in the same way, but it didn't kick in for the find query.
      • Constant folding should produce the same value as direct execution - otherwise it is incorrect to constant fold an expression.

            Assignee:
            backlog-query-execution [DO NOT USE] Backlog - Query Execution
            Reporter:
            timour.katchaounov@mongodb.com Timour Katchaounov
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved: