-
Type: Bug
-
Resolution: Fixed
-
Priority: Major - P3
-
Affects Version/s: 8.1.0-rc0, 8.0.0-rc12
-
Component/s: None
-
None
-
Query Optimization
-
Fully Compatible
-
ALL
-
v8.0
-
QO 2024-07-22
-
200
SERVER-88087 introduced a rewrite to merge multiple compatible topN/bottomN expressions together to reuse the same sort. However, this rewrite generates the wrong values for missing fields.
Simple repro: for this document: {a: null}, the group below should return {a: null, b1: [null], b2: [null]}; however, it actually returns {a: null, b1: [], b2: []}
{ $group: { _id: "$a", b1: {$bottomN: {n: 1, sortBy: {a: -1, b: -1}, output: "$b"}}, b2: {$bottomN: {n: 1, sortBy: {a: -1, b: -1}, output: "$b"}} } }
With SERVER-88087, this group gets rewritten to:
{ '$group': { _id: '$a', bns: { '$bottomN': { n: { '$const': 1 }, output: { b1: '$b', b2: '$b' }, sortBy: { a: -1, b: -1 } } } } }
In order to recombine the output of the $group to be as expected, we add a project like this at the end:
{$project: { _id: true, b1: { '$ifNull': [ '$bns.b1', { '$const': null } ]}, b2: { '$ifNull': [ '$bns.b2', { '$const': null } ]}, }}
The problem is when the value for "b1" or "b2" selected by $bottomN is missing, due to array traversal semantics, the expression '$bns.b1' doesn't evaluate to a missing value, but rather to an empty array.
So, for this document: {a: null}, the original $bottomN would return:
{a: null, b1: [null], b2: [null]}While the rewritten $group would return:
{a: null, bns: [{}]}
And the "$bns.b1" paths in the project would evaluate to [], which would not get converted to a null.
- is caused by
-
SERVER-88087 Rewrite many $topNs/$bottomNs that have the same sort pattern so that it only creates one sort key
- Closed
- is related to
-
SERVER-92325 $group + $topN sometimes returns different results
- Closed
-
SERVER-90017 Parse firstN/lastN/minN/maxN/topN/bottomN with n = 1 into a corresponding first/last/min/max/top/bottom
- Closed