[SERVER-67031] Different result with and without optimization and $convert Created: 06/Jun/22  Updated: 06/Dec/22  Resolved: 09/Jun/22

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

Type: Bug Priority: Major - P3
Reporter: Timour Katchaounov Assignee: Backlog - Query Optimization
Resolution: Won't Fix Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Related
Assigned Teams:
Query Optimization
Operating System: ALL
Steps To Reproduce:

// Start the server with: --setParameter enableTestCommands=1
 
db.coll.drop();
db.coll.insertMany([
    {_id: 984, "str": "Croatian Kuna 1080p", "num": NumberInt(18960), "date": new Date("2019-03-31T03:32:37.390Z"), "array": [NumberDecimal("763.2229799659431"), NumberLong("38951"), new Date("2019-08-11T15:41:03.761Z"), {_id: 985, "str": "Ergonomic Garden Somoni", "date": new Date("2019-10-15T02:56:07.777Z"), "array": [new Date("2019-11-20T01:02:23.921Z"), new Date("2019-12-23T00:51:52.521Z"), [], new Date("2019-03-19T00:19:20.797Z"), NumberInt(46663), "TCP", {_id: 986, "str": "yellow Crescent sensor", "num": NumberInt(13305), "date": null, "array": ["mindshare Frozen hacking"], "obj": {_id: 987, "num": NumberInt(26143), "date": new Date("2019-09-05T05:32:55.088Z"), "array": [null], "obj": {_id: 988, "str": "quantifying Belarussian Ruble Associate", "obj": {}, }, }, }, null, "Chile 6th generation Unbranded", NumberLong("87817")], }, NumberDecimal("280.59125732802977"), null], }, // 160
    {_id: 989, "str": "synthesize action-items payment", "num": NumberInt(49760), "date": new Date("2019-06-23T22:27:29.468Z"), "array": [], "obj": {_id: 990, "str": "leverage", "num": NumberInt(97197), "array": [NumberLong("47255"), new Date("2019-06-22T17:47:36.887Z"), "Extensions Generic Steel Chicken", new Date("2019-02-18T07:22:08.091Z"), {_id: 991, "str": null, "date": null, "array": [], "obj": {}, }, "JBOD", "microchip", NumberDecimal("-523.820147728908"), NumberDecimal("-789.7990900756054"), new Date("2019-01-16T07:47:01.888Z"), NumberDecimal("621.492090092429"), {_id: 992, "date": new Date("2019-11-23T14:21:45.032Z"), "array": [{_id: 993, "str": "approach Auto Loan Account", "array": [NumberLong("98838"), "mesh"], }, []], }], "obj": {_id: 994, "num": NumberInt(70512), "date": new Date("2019-06-04T09:17:34.995Z"), "obj": {_id: 995, "str": "User-friendly", "num": NumberLong("32545"), "date": new Date("2019-03-06T23:12:36.185Z"), "obj": {_id: 996, "num": NumberLong("88800"), "date": new Date("2019-10-30T11:33:44.387Z"), "array": [null, new Date("2019-03-02T14:42:08.727Z"), [null, NumberInt(15167)], null], "obj": {_id: 997, "str": null, "num": NumberDecimal("-333.53906345304574"), "date": null, "array": [], "obj": {}, }, }, }, }, }, }, // 161
    {_id: 998, "str": "Licensed Inverse Open-architected", "num": NumberLong("49026"), "date": new Date("2019-04-23T19:45:00.355Z"), "array": [NumberInt(92632), "copying e-business deliver", NumberInt(69368), new Date("2019-12-04T19:10:35.993Z"), NumberDecimal("-749.514929614997"), "Cape Brand bus", new Date("2019-11-20T12:02:12.166Z"), [{_id: 999, "str": "withdrawal Global", "num": NumberInt(19686), "date": new Date("2019-05-19T18:21:42.631Z"), }, "backing up Table Frozen", null, NumberLong("3843"), {_id: 1000, "str": "cross-platform", "num": NumberDecimal("-34.01956168955553"), "date": null, "array": [new Date("2019-11-07T23:52:14.804Z")], "obj": {_id: 1001, "num": NumberInt(53845), "date": new Date("2020-01-06T18:47:36.468Z"), "array": [], "obj": {}, }, }, "Table Wooden", [NumberLong("66770"), {_id: 1002, "num": NumberLong("7171"), "date": new Date("2019-06-05T09:35:47.022Z"), "array": [], "obj": {}, }]]], "obj": {_id: 1003, "str": null, "num": NumberInt(47428), "date": new Date("2019-10-05T23:23:29.202Z"), "obj": {_id: 1004, "str": "Bedfordshire cutting-edge", "array": null, }, }, }, // 162
    {_id: 1005, "str": "Buckinghamshire Bermuda Managed", "num": NumberLong("60679"), "date": new Date("2019-11-27T06:13:20.735Z"), "obj": {_id: 1006, "str": "brand connecting indigo", "num": NumberInt(50475), "date": new Date("2019-03-31T01:49:38.835Z"), "array": [[NumberLong("25282"), {_id: 1007, "str": "Auto Loan Account", "num": NumberDecimal("671.031524759615"), "array": [new Date("2019-07-11T14:11:34.684Z")], "obj": {_id: 1008, "str": "Usability benchmark e-business", "date": new Date("2019-08-24T15:47:46.090Z"), "array": [new Date("2019-05-10T17:12:52.203Z")], }, }, "Outdoors", new Date("2019-08-27T23:51:45.018Z"), {_id: 1009, "num": NumberLong("61414"), "date": new Date("2019-04-23T02:27:03.346Z"), "array": [], "obj": {_id: 1010, "str": null, "num": NumberInt(847), "date": new Date("2019-07-03T12:41:40.390Z"), "obj": {}, }, }], NumberInt(64843), "solid state unleash", "modular Nebraska", "withdrawal Oklahoma", NumberInt(82902), new Date("2019-11-24T10:15:07.274Z"), [], null, "deliver"], "obj": {}, }, }, // 163
]);
 
const pipeline =
[{$sortByCount: {$ifNull: [{$convert:
                            {input: {$add: ["$obj.obj.obj.obj.obj.num", new Date("2019-11-18T19:54:56.205Z"), NumberDecimal("-585.7477314796557")]},
                                 to: 2,
                                 onNull: {$arrayToObject: [[{k: "matrix", v: {$dateTrunc: {date: ObjectId('507f191e810c19729de860ea'), unit: "day", binSize: NumberLong("89785479")}}}]]}}},
                               []
                              ]
                }}];
 
db.coll.aggregate(pipeline);
db.coll.explain().aggregate(pipeline);
 
db.adminCommand({configureFailPoint: "disableMatchExpressionOptimization", mode: "alwaysOn"});
db.adminCommand({configureFailPoint: "disablePipelineOptimization", mode: "alwaysOn"});
 
db.coll.aggregate(pipeline);
db.coll.explain().aggregate(pipeline);

Participants:
Linked BF Score: 49

 Description   

The following script shows that the same statement produces different results with and without optimization. I am not sure which expression is the cuplrit, but most likely it is $convert.

With optimization the result is:

[
  { _id: { matrix: ISODate("2000-01-01T00:00:00.000Z") }, count: 3 },
  { _id: '2019-11-18T19:54:55.285Z', count: 1 }
]

without optimization the result is:

[
  { _id: { matrix: ISODate("2000-01-01T00:00:00.000Z") }, count: 3 },
  { _id: '2019-11-18T19:54:55.286Z', count: 1 }
]

Notice the difference in the two timestamps: one ends in '285Z', the other one as '286Z'.



 Comments   
Comment by Steve Tarzia [ 09/Jun/22 ]

timour.katchaounov@mongodb.com I already updated the BF to link to the new fuzzer ticket: TIG-4194.

Comment by Timour Katchaounov [ 09/Jun/22 ]

steve.tarzia@mongodb.com I have to update somehow the related BF. What is the task to handle approximate date matching in fuzzer?

Comment by Timour Katchaounov [ 09/Jun/22 ]

steve.tarzia@mongodb.com yes, makes sense, especially if there is a consensus that such rounding errors are acceptable in date operations. The reason I filed this as a server bug is because I don't remember ever seeing any bugs like this one in my SQL-optimization practice. Perhaps we are doing optimizations that are not common for SQL systems, or I this due to my limited experience. Anyway, let's fix this via fuzzer.

Should I close this one as "Not a bug"?

Comment by Steve Tarzia [ 08/Jun/22 ]

timour.katchaounov@mongodb.com this looks like a numerical consistency due to an arithmetic rewrite during optimization.  Would you be satisfied if we resolved this by introducing fuzzy timestamp comparisons in the agg fuzzer (like we do for floats)?

In other words, would you agree this is not a bug, but a false alarm in the fuzzer?

Generated at Thu Feb 08 06:07:06 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.