[SERVER-63518] Equivalent find and agg queries produce different plans and different results from a decimal field Created: 10/Feb/22  Updated: 06/Dec/22  Resolved: 10/Feb/22

Status: Closed
Project: Core Server
Component/s: Query Execution
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Timour Katchaounov Assignee: Backlog - Query Execution
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Duplicate
duplicates SERVER-62485 Match the SBE $add behavior to the cl... Closed
Assigned Teams:
Query Execution
Operating System: ALL
Steps To Reproduce:

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"
        }
}

Participants:
Linked BF Score: 20

 Description   

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.


 Comments   
Comment by Kyle Suarez [ 10/Feb/22 ]

Closing as a duplicate of SERVER-62485.

Comment by Yoon Soo Kim [ 10/Feb/22 ]

kyle.suarez, Yes, it is.
In both aggregate() & find(), constant folding kicks in but for aggregate, the pipeline is pushed down to the SBE after constant folding and the SBE binary $add (genericArithmeticOp<Addition>()) does the addition. While doing addition, the SBE binary $add promotes the constant-folded double to a decimal with rounding option 15 digits, due to which we see lower digits chopped off.

For the find query, the query is not pushed down to the SBE probably because $pow is not compatible with the SBE (same for aggregate but I don't know why the pipeline can be pushed down and the find query can NOT be pushed down). So we use the classic engine's double double sum algorithm (ExpressionAdd::evaluate) which preserves 34 digits since the folded const double is added to nonDecimalTotal and at the final step, the nonDecimalTotal is promoted to a decimal with 34 digit rounding option.

I don't think it's the constant folding to blame but it's the inconsistent behavior of genericArithmeticOp<Addition>() which promotes a double to a decimal with 15 digit rounding option.

Comment by Kyle Suarez [ 10/Feb/22 ]

yoonsoo.kim, is this the same underlying cause as one of the other SBE Decimal tickets you've filed?

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