Info

I have a collection with elements of the following structure: { device: "M57906", date: some date } and one index { device: 1, date: 1 } The query with $bottom does not return the correct result. This only happens when the index exists. h4s> db.events.aggregate([{ $sort: { device: 1, date: 1 } }, { $group: { _id: "$device", obj: { $bottom: { output: "$date", sortBy: { date: -1 } } } } }]) [ { _id: 'M57906', obj: ISODate('2025-08-28T09:46:33.017Z') } ] h4s> db.events.aggregate([{ $sort: { device: 1, date: 1 } }, { $group: { _id: "$device", obj: { $bottomN: { n: 1, output: "$date", sortBy: { date: -1 } } } } }]) [ { _id: 'M57906', obj: [ ISODate('2025-07-06T00:00:01.305Z') ] } ] winning plan for $bottom query: isCached: false, stage: 'PROJECTION_COVERED', transformBy: { date: 1, device: 1, _id: 0 }, inputStage: { stage: 'DISTINCT_SCAN', keyPattern: \{ device: 1, date: 1 } , indexName: 'device_1_date_1', isMultiKey: false, multiKeyPaths: { device: [], date: [] }, isUnique: false, isSparse: false, isPartial: false, indexVersion: 2, direction: 'backward', indexBounds: { device: [ '[MaxKey, MinKey]' ], date: [ '[MaxKey, MinKey]' ] } } winning plan for $bottomN: n: 1 query { isCached: false, queryPlan: { stage: 'GROUP', planNodeId: 3, inputStage: { stage: 'PROJECTION_COVERED', planNodeId: 2, transformBy: \{ date: true, device: true, _id: false } , inputStage: { stage: 'IXSCAN', planNodeId: 1, keyPattern: \{ device: 1, date: 1 } , indexName: 'device_1_date_1', isMultiKey: false, multiKeyPaths: { device: [], date: [] }, isUnique: false, isSparse: false, isPartial: false, indexVersion: 2, direction: 'forward', indexBounds: { device: [ '[MinKey, MaxKey]' ], date: [ '[MinKey, MaxKey]' ] } } } }, slotBasedPlan: { slots: '$$RESULT=s9 env: { s4 = SortSpec( {"date" : -1} ) }', stages: '[3] project [s9 = newBsonObj("_id", s6, "obj", s8)] \n' + '[3] project [s8 = aggBottomNFinalize(s7, s4)] \n' + '[3] group [s6] [s7 = aggBottomN(s4, null, getSortKeyDesc(s2), (s2 ?: null)) init{[[], 0ll, 1ll, 0, 104857600, true]}] spillSlots[s5] mergingExprs[aggBottomNMerge(s5, s4)] \n' + '[3] project [s6 = (s1 ?: null)] \n' + '[1] ixseek KS(0A0A0104) KS(F0F0FE04) none s3 none none lowPriority [s1 = 0, s2 = 1] @"edc48567-cea3-4782-97fe-7173105114f2" @"device_1_date_1" true ' } } $top is also broken in the same way.

Top User Comments

JIRAUSER1283477 commented on Fri, 12 Sep 2025 22:03:54 +0000: test> db.events.insertMany([ | // Device A events | { device: "A", date: ISODate("2024-01-10T00:00:00Z") }, | { device: "A", date: ISODate("2024-01-05T00:00:00Z") }, | { device: "A", date: ISODate("2024-02-15T00:00:00Z") }, | | // Device B events | { device: "B", date: ISODate("2024-02-01T00:00:00Z") }, | { device: "B", date: ISODate("2024-01-25T00:00:00Z") }, | { device: "B", date: ISODate("2024-03-03T00:00:00Z") }, | | // Device C events | { device: "C", date: ISODate("2024-03-10T00:00:00Z") }, | { device: "C", date: ISODate("2024-03-08T00:00:00Z") }, | { device: "C", date: ISODate("2024-02-28T00:00:00Z") } | ]); | { acknowledged: true, insertedIds: { '0': ObjectId('68c4980b2d1a344ff8a8d94f'), '1': ObjectId('68c4980b2d1a344ff8a8d950'), '2': ObjectId('68c4980b2d1a344ff8a8d951'), '3': ObjectId('68c4980b2d1a344ff8a8d952'), '4': ObjectId('68c4980b2d1a344ff8a8d953'), '5': ObjectId('68c4980b2d1a344ff8a8d954'), '6': ObjectId('68c4980b2d1a344ff8a8d955'), '7': ObjectId('68c4980b2d1a344ff8a8d956'), '8': ObjectId('68c4980b2d1a344ff8a8d957') } } test> db.events.aggregate([ | { $sort: { device: 1, date: 1 } }, | { | $group: { | _id: "$device", | obj: { $bottom: { output: "$date", sortBy: { date: -1 } } } | } | } | ]) | [ { _id: 'B', obj: ISODate('2024-01-25T00:00:00.000Z') }, { _id: 'C', obj: ISODate('2024-02-28T00:00:00.000Z') }, { _id: 'A', obj: ISODate('2024-01-05T00:00:00.000Z') } ] test> db.events.aggregate([ | { $sort: { device: 1, date: 1 } }, | { | $group: { | _id: "$device", | obj: { $bottomN: { n: 1, output: "$date", sortBy: { date: -1 } } } | } | } | ]) | [ { _id: 'B', obj: [ ISODate('2024-01-25T00:00:00.000Z') ] }, { _id: 'C', obj: [ ISODate('2024-02-28T00:00:00.000Z') ] }, { _id: 'A', obj: [ ISODate('2024-01-05T00:00:00.000Z') ] } ] Sorry I was unable to reproduce this behaviour on 8.0.13. Could you please provide some more detailed reproduction steps, including the specific test data you are using to get these results? Thanks.

Steps to Reproduce