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

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: Query Execution
    • Labels:
      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: